Power BI - How to create another table from original source

Question

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.

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).

Is this possible on the power query?!

Andrey Nikolov Andrey Nikolov · Accepted Answer · 2020-07-22T18:59:24

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:

Power BI - How to create another table from original source

1 Answers