CODEWITHSUNDEEP
powerbipowerquery
dee
dee

Reputation: 147

Power BI - How to create another table from original source

I wrote a query that pulls data into Power BI. I was wondering if I can create another query that pulls in the original data without certain columns. I know that I can delete a column but I was wondering if I can remove a column and have other columns aggregated. I want to do this from the back-end (PowerQuery). I know I can create another query without including the other column but since this is real-time data, I need to pull the data from the original query.

This is the original data.

enter image description here

This is what I am trying to achieve. I want to remove the column 'Code' but as well as having the other columns aggregated (Calls, Invalid) and distinct columns (Date, Name, Connection Type).

enter image description here

Is this possible on the power query?!

Upvotes: 0

Views: 1212

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

Of course it is possible. Here is an example M code how to do that in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndKxCoMwEAbgd8ksohdN5y527VDoIA5BQwnYCufSx28oKZHmzqQdJEL4uPvv0vcCoDyUUEElCnExT726s3bf1aKZ3Hm8G7Sjdn/yfTMUtAHSNKQ5mQVvVrOV2oT6r5b0ajbrmlHurNF+B+tQP0bj++aJzCfdvKCdtqGi9iAB2Vz0wgJsyDFu+qz5pxEV22dsuH0myQ4lacKi2x9yBaVIBaSKt5bTYbwy9tmT6pMrGqJKQMVBP5PhBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, Code = _t, #"Connection Type" = _t, Country = _t, Calls = _t, Invalid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Code", type text}, {"Connection Type", type text}, {"Country", type text}, {"Calls", Int64.Type}, {"Invalid", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Code"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Name", "Connection Type", "Country"}, {{"Calls", each List.Sum([Calls]), type text}, {"Invalid", each List.Sum([Invalid]), type text}})
in
    #"Grouped Rows"

Table.RemoveColumns will remove the Code column and Table.Group will group the data on the specified columns (Date, Name, Connection Type and Country) and aggregate the data, sum in this case (Calls and Invalid).

You can do this using the UI only. In Power Query Editor, right click the title of Code column and select Remove. Then from Transform tab click on the leftmost button Group By and fill it as follows:

enter image description here

Upvotes: 1

Related Questions