Power Query pivot

[sabd80]

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?

[v-sgandrathi]

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

[Tahreem24]

@sabd80 You could try with matrix visual with below settings:

[sabd80]

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

[govind_021 · Accepted solution]

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

[sabd80]

@govind_021  thank you so much for your solution, it worked perfectly.

[Irwan]

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.

[danextian]

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