Forum Discussion

sabd80's avatar
sabd80
Helper IV
1 year ago
Solved

Power Query pivot

Hi,
I have below data in Power BI:


 

I would like to pivot the data to below in another table:

 

How can I do that?

Need Help
  • govind_021's avatar
    govind_021
    1 year ago

    Hi , sabd80 
    Please , follow below steps
    1) Select Volume , Gross Value , Net Value column with help of ctrl key 
    then go to "Transform" and select unpivot columns then Unpivot only Selected columns 

    then your data will look like this 

    2) After That select source and  attribute column with ctrl key together and go to trasnform and select
    merge columns and in separator choose space from the option.


    Then your data will look like this 

    3)  After that select Newly merged column

    and click on Transform data "Pivot" and in value section select Value column
    and your data will be in desired state 

     

    Best Regards,
    Govind Sapkade
    Data Analyst | Power BI Enthusiast | Microsoft PL 300 Certified Power BI Data Analyst | MS Fabric Enthusiast
    🎥Subscribe to my youtube channel for hands on tutorials : YouTube

    📊Let’s connect on Linkdin : Linkdin

     

7 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi sabd80,

     

    You will need to create several measures and add them to a PowerPivot  pivot table. Example:

    Actual Volume =
CALCULATE (
    SUM ( 'table'[Volume] ),
    KEEPFILTERS ( 'table'[Source] = "Actual" )
)

     

     

  • govind_021's avatar
    govind_021
    Super User
    1 year ago

    Hi , sabd80 
    Please , follow below steps
    1) Select Volume , Gross Value , Net Value column with help of ctrl key 
    then go to "Transform" and select unpivot columns then Unpivot only Selected columns 

    then your data will look like this 

    2) After That select source and  attribute column with ctrl key together and go to trasnform and select
    merge columns and in separator choose space from the option.


    Then your data will look like this 

    3)  After that select Newly merged column

    and click on Transform data "Pivot" and in value section select Value column
    and your data will be in desired state 

     

    Best Regards,
    Govind Sapkade
    Data Analyst | Power BI Enthusiast | Microsoft PL 300 Certified Power BI Data Analyst | MS Fabric Enthusiast
    🎥Subscribe to my youtube channel for hands on tutorials : YouTube

    📊Let’s connect on Linkdin : Linkdin

     

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello sabd80 

     

    here is just a simple example just to give rough idea.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYq7DYAwDAV3eXUKf8QAsIblIgREQ5nsj60UEc2ddDozMDMKamIjohDTcua99VFfeDGISIQzIXMSWpb/rKoRWkLnpLSc+RjXc3e4fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Product = _t, Volume = _t, Gross = _t, Net = _t, Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Product", type text}, {"Volume", Int64.Type}, {"Gross", Int64.Type}, {"Net", Int64.Type}, {"Source", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Product", "Source"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Source", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"ID", type text}}, "en-ID"),{"ID", "Product"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value", List.Sum),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Merged.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Merged.1.1", "Merged.1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1.1", Int64.Type}, {"Merged.1.2", type text}})
    in
    #"Changed Type1"

    here are the steps:

    1. Unpivot the table

     

    2. Merge so you get your header description

     

    3. Merge ID and product since you dont want to those columns get pivoted

     

    4. Pivot back the table

     

    5. Split ID and Product

     

    this might not the most efficient way, but you can tweak it to your need. Also, dont mind the null value because i didnot type all your data in screenshot.

     

    otherwise, danextian 's solution surely works

     

    Hope this will help.
    Thank you.

    • sabd80's avatar
      sabd80
      Helper IV
      1 year ago

      Tahreem24 , I am aware of that, I meant it on the row level. But thanks for your reply.

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi, sabd80 
    Thanks for reaching out to the Microsoft Fabric Community forum

     

    I trust the govind_021 response is accurate and will address your issue.

    If you have any further questions or updates regarding your issue, feel free to ask, and we will look into that.

    If the Super User's answer meets your requirements, please consider marking it as the Accepted solution.

     

    Regards,
    Sahasra