Replicate Excel Pivot table in Query Editor

[arpit60]

Dear Members,

Is thier any way to replicate Excel pivot table exactly in Query Editor in Power BI Desktop ?

Reason for this is beacuse after getting all the data of all months for particular Customer / Material in same row, I will be able to apply all the formulas.

Raw Data Excel Table

Cust	Material	Calendar Year/Month	Qty	Sales	Margin
A	l	Jan 21	12,000	49,545	19,062
A	l	Feb 21	6,000	25,419	9,615
A	l	Mar 21	8,000	34,262	13,177

I want Data in below format, which can be done in pivot table in few seconds

		Jan 21			Feb 21			Mar 21
Cust	Material	Qty	Sales	Margin	Qty	Sales	Margin	Qty	Sales	Margin
A	l	12,000	49,545	19,062	6,000	25,419	9,615	8,000	34,262	13,177

As a workaround for now, I am importing file in power BI after creating pivot in excel and pasting the result in seperate sheettab.

Thanks and Regards,

Arpit

[v-kelly-msft · Accepted solution]

Hi @arpit60 ,

Using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcoBYiNDXa/EPCDj0AJDIx0DAwMw08RSx9TEFCJqqWNgZqQUq4Oixy01CSwLggpmcH1GpjomhpYIGUsdM0NTdL2+iUUIFRZwvcYmOkZAi8B2GusYmpsrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cust = _t, Material = _t, #"Calendar Year/Month" = _t, Qty = _t, Sales = _t, Margin = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cust", type text}, {"Material", type text}, {"Calendar Year/Month", type text}, {"Qty", Int64.Type}, {"Sales", Int64.Type}, {"Margin", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Calendar Year/Month"}, "Attribute", "Value"),
    #"Transposed Table" = Table.Transpose(#"Unpivoted Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"21-Jan", type text}, {"21-Jan_1", type text}, {"21-Jan_2", type any}, {"21-Jan_3", type any}, {"21-Jan_4", type any}, {"21-Feb", type text}, {"21-Feb_5", type text}, {"21-Feb_6", type any}, {"21-Feb_7", type any}, {"21-Feb_8", type any}, {"21-Mar", type text}, {"21-Mar_9", type text}, {"21-Mar_10", type any}, {"21-Mar_11", type any}, {"21-Mar_12", type any}})
in
    #"Changed Type1"

Then you will  get the pivot table you need:

You only modify the column names per your request.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[arpit60]

@aj1973 and @amitchandak thanks, I am familiar with Matrix visual.

My end goal is to create above view in Query editor apply the data and than i have lot of measures and formulas.

All my formulas work only if i have details in same row.

suppose if i create this view in matrix visual can i copy that data in some kind of table and apply all the formulas on it  ?

all my other visuals are dependent on this formula applied final data.

[aj1973]

All what's done in your excel can be done in Power Bi desktop. Power Query is to clean and transform data not to make calculationa and formulas. DAX is your best friend, use it to replicate your formulas and then you can apply them into the Matrix.  

[arpit60]

i can apply fomrula only if i have all the info in single row, this is the part in am struggling with.

[aj1973]

I don't agree with you. If you really want to use Power BI then obviously you will need to learn it and learn DAX.

Else, if you want to get help

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Good luck

[aj1973]

Hi @arpit60 

You can replicate it in the Canvas of the desktop

[amitchandak]

@arpit60 , You can get this kind of display in matrix ?

what is the issue with that