Pivot 2 Values Columns at once

[o59393]

Hi all

Is it possible to pivot 2 Values Columns at once. In the example below I need to do it for the numeric columns (Available Production Hours and OU Demand). The pivot column is Period[445]:

After I pivot one of them I get this M code:

= Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Period[Month 445]", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Period[Month 445]", type text}}, "en-US")[#"Period[Month 445]"]), "Period[Month 445]", "OU Demand Produced By Plant & Line", List.Sum)

Is it possible to include in the code above the production hours?

Thanks.

[ronrsnfld]

This seems like an X Y Problem. Perhaps if you provided a data sample as text which can be copy/pasted, and a screenshot of your desired results, someone might be able to better assist. 

[o59393]

Hi @ronrsnfld 

See attached the original and desired state: data_sample_1.xlsx

Thanks!

[Anonymous · Accepted solution]

Hi @o59393 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vdQ7TwMxDADgv3KKxFZc27HzYAMkpgJVJ+DUgaHDSTdV/H/hExL41JV6cBTdI5+cxB7HRFvaMnIe8A4xbdKbxbvFh8Xj62HYiU3I4mm3vHrY23B4saFLGZ6neZ7m6et0tgf7++VLqssCp8/zsPykemMjK2boxWYFNR03Y+IItTECNafmAFUpN8g+VwlQcxYGJadqhErSoYpTS4TKTaB0p9YAlbgjMDu1BahSoPi71ENOFXF9qoQRG4y9ejOiMVFlBl81FNGYbqs0kNUG/6jy/yr/5dpEwaMcgDJpW9/gHKFW60u+VCUCzaTQcrpowVfeYK7Ami5a8HVVZQVfrTXiAnet0H2qLSJVLA3Iqz1CLWSN6bdujt8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Period[Month 445]" = _t, #"Country Manufacturing" = _t, #"Bottler Of Manufacturing" = _t, #"Manufacturing plant of production" = _t, #"Manufacturing line" = _t, Category = _t, #"Package[Container Material]" = _t, #"Package[Container Type]" = _t, #"Package[L1.4 - Refillability]" = _t, #"Package[Primary Container Volume Capacity]" = _t, #"Ship From[L1.2 Bottler Of Sale]" = _t, #"Bottles per minute (theoric)" = _t, Attribute = _t, #"Line Utilization (real)" = _t, #"OU Demand Produced By Plant & Line" = _t, #"Available Production Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period[Month 445]", type datetime}, {"Country Manufacturing", type text}, {"Bottler Of Manufacturing", type text}, {"Manufacturing plant of production", type text}, {"Manufacturing line", type text}, {"Category", Int64.Type}, {"Package[Container Material]", type text}, {"Package[Container Type]", type text}, {"Package[L1.4 - Refillability]", type text}, {"Package[Primary Container Volume Capacity]", type text}, {"Ship From[L1.2 Bottler Of Sale]", type text}, {"Bottles per minute (theoric)", Int64.Type}, {"Attribute", type text}, {"Line Utilization (real)", Percentage.Type}, {"OU Demand Produced By Plant & Line", type number}, {"Available Production Hours", Int64.Type}}),
    #"Pivoted Column_Demand" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Period[Month 445]", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Period[Month 445]", type text}}, "en-US")[#"Period[Month 445]"]), "Period[Month 445]", "OU Demand Produced By Plant & Line"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column_Demand", "Type", each "OU Demand"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Available Production Hours"}),
    #"Pivoted Column_available" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Period[Month 445]", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Period[Month 445]", type text}}, "en-US")[#"Period[Month 445]"]), "Period[Month 445]", "Available Production Hours"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column_available",{"OU Demand Produced By Plant & Line"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Type", each "available"),
    #"Appended Query" = Table.Combine({#"Removed Columns", #"Added Custom1"})
in
    #"Appended Query"

Output

I add a type column to distinguish two values

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.