Forum Discussion

arpit60's avatar
arpit60
Regular Visitor
5 years ago
Solved

Replicate Excel Pivot table in Query Editor

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

 

CustMaterialCalendar Year/MonthQtySalesMargin
AlJan 21 12,000 49,545 19,062
AlFeb 21    6,000 25,419    9,615
AlMar 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  
CustMaterial  Qty  Sales  Margin  Qty  Sales  Margin  Qty  Sales  Margin
Al 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

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

7 Replies

  • arpit60's avatar
    arpit60
    Regular Visitor
    5 years ago

    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's avatar
      aj1973
      Community Champion
      5 years ago

      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's avatar
        arpit60
        Regular Visitor
        5 years ago

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!