Use Pivots to properly arrange my data

[sdukes]

Hello all,

I'm missing what I'm hoping is a simple step here...

I have my data in this format: 

Location	Type	Jan	Feb	Mar
A	1	0.1	0.2	0.1
A	2	0.2	0.1	0.1
A	3	0.1	0.1	0.12
B	1	0.1	0.12	0.2
B	2	0.12	0.2	0.2
B	3	0.2	0.05	0.1
C	1	0.2	0.1	0.05
C	2	0.1	0.05	0.1
C	3	0.05	0.2	0.12

and I wan't to get it to this format:

Location	Month	Type 1	Type 2	Type 3
A	Jan	0.1	0.2	0.1
A	Feb	0.2	0.1	0.1
A	Mar	0.1	0.1	0.12
B	Jan	0.1	0.12	0.2
B	Feb	0.12	0.2	0.05
B	Mar	0.2	0.2	0.1
C	Jan	0.2	0.1	0.05
C	Feb	0.1	0.05	0.2
C	Mar	0.05	0.1	0.12

Any advice would be appreciated!

Thank you all,

Shmuel

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location", "Type"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Type", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Type", type text}}, "en-IN")[Type]), "Type", "Value")
in
    #"Pivoted Column"

Hope this helps.

[sdukes]

Thank you! All I was missing was a pivot! 

[Ashish_Mathur]

You are welcome.

[v-yiruan-msft]

Hi @sdukes ,

You can applied the unpivot and pivot steps in Power Query Editor to achieve it, the specific codes as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYgM9CGkEZcfqQOSMUERR5YxRRMGkEVjSCc1QQ5gpMEkjVGFUSWMkUQNTJDud4cYiuweoBCaJJoyq0xhZGO6A2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Type = _t, Jan = _t, Feb = _t, Mar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Jan", "Feb", "Mar"}, "Attribute", "Value"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Unpivoted Only Selected Columns", {"Location", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Value.1", type text}}, "zh-CN"),{"Attribute.1", "Value.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Location", "Attribute", "Merged", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Applied steps in Power Query EditorBest Regards

Rena

[parry2k]

@sdukes check my blog post here which pretty much talk about the similar use case

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡