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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.