Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gkalandadze
Frequent Visitor

Pivot or unpivot?

Hello,

 

I use data from 2 different sources. 

I want to compare these value in STACKED area chart. but as long as they are different types of tables , I couldn't figure out how compare them. how can I unpivot or pivot them to use them? i'm quite new please explain it to me this is very urgent. thanks in advance

Annotation 2019-03-16 180223.jpgSharedScreenshotr.jpg

2 ACCEPTED SOLUTIONS
Nishantjain
Continued Contributor
Continued Contributor

Try this solution on table 2, assuming you have 2 index and 2 value columns. Let me know if you have more and I will try again. 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NLJR2lAH8gYQhiOAIJS6VYHYgCIxQFRjAFhgZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t, Value = _t, Index2 = _t, Value2 = _t]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Value", type text}}, "en-GB"),{"Index", "Value"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Value2", type text}}, "en-GB"),{"Index2", "Value2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.2"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns2", {{"Merged.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Merged.2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.2.1", "Merged.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.2.1", type text}, {"Merged.2.2", Int64.Type}})
in
    #"Changed Type2"

 

 

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

 

On the first table, apply this M code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", type text}, {"Value", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value.1"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Date.ToText([Date])&[Attribute]),
    Partition = Table.Group(#"Added Custom", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Attribute", "Value.1", "Index"}, {"Date", "Attribute", "Value.1", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Partition", "Custom.1", each Number.ToText([Index])&[Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Index", "Custom"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}, {"Custom.1", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Custom.1]), "Custom.1", "Value.1")
in
    #"Pivoted Column"

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

On the first table, apply this M code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", type text}, {"Value", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value.1"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Date.ToText([Date])&[Attribute]),
    Partition = Table.Group(#"Added Custom", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Attribute", "Value.1", "Index"}, {"Date", "Attribute", "Value.1", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Partition", "Custom.1", each Number.ToText([Index])&[Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Index", "Custom"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}, {"Custom.1", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Custom.1]), "Custom.1", "Value.1")
in
    #"Pivoted Column"

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks man! you helped me a lot!Smiley Happy

Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nishantjain
Continued Contributor
Continued Contributor

Try this solution on table 2, assuming you have 2 index and 2 value columns. Let me know if you have more and I will try again. 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NLJR2lAH8gYQhiOAIJS6VYHYgCIxQFRjAFhgZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t, Value = _t, Index2 = _t, Value2 = _t]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Value", type text}}, "en-GB"),{"Index", "Value"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Value2", type text}}, "en-GB"),{"Index2", "Value2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.2"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns2", {{"Merged.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged.2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Merged.2", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.2.1", "Merged.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.2.1", type text}, {"Merged.2.2", Int64.Type}})
in
    #"Changed Type2"

 

 

Yes , there are 6 indexes and 6 values overal. could you please make again? thank you!

@gkalandadze 

 

I would recommend you go through the steps that I have applied for 2 indexes. Spend some time in trying to extend it to 6. It will help you to understand how to use Power Query. If you still struggle, send me the code of your attempt and I will try to fix it for you

 

Thanks

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.