Forum Discussion
Solved
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
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"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.
6 Replies
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!
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
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.
Thanks man! you helped me a lot!:smileyhappy:
Thank you.
