cancel
Showing results for 
Search instead for 
Did you mean: 
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
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.