Forum Discussion

QZ's avatar
QZ
Helper I
3 years ago
Solved

(M language)How to change columns to rows by Power Query

I have original data like bellow:   May I know how to transform to bellow format?   Actually, my final purpose is create a report(refer bellow picture) base on the data.  
Need Help
  • KeyurPatel14's avatar
    KeyurPatel14
    3 years ago

    Hi QZ ,
    Please try below M Code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdU0lFyS0wuyS+qVACxfROTMzLzUkFMQz0IaQQmjZVidQjpAqk0Ausy0oOwidFlDGQag3UZg3UZo+oywudCEzBpCibNiNAFcZUJmDQFk8TogrjQBEyagkmgrlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Process Date" = _t, Area = _t, Machine = _t, EOP1 = _t, EOP2 = _t, EOP3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Process Date", type date}, {"Area", type text}, {"Machine", type text}, {"EOP1", type number}, {"EOP2", type number}, {"EOP3", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Process Date", "Area", "Machine"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Machine]), "Machine", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Period"}})
    in
    #"Renamed Columns"

    If this helps then mark it as a solution.
    Thank you.

    Sample output:

     

KeyurPatel14's avatar
KeyurPatel14
Responsive Resident
3 years ago

Hi QZ ,
Please try below M Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdU0lFyS0wuyS+qVACxfROTMzLzUkFMQz0IaQQmjZVidQjpAqk0Ausy0oOwidFlDGQag3UZg3UZo+oywudCEzBpCibNiNAFcZUJmDQFk8TogrjQBEyagkmgrlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Process Date" = _t, Area = _t, Machine = _t, EOP1 = _t, EOP2 = _t, EOP3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Process Date", type date}, {"Area", type text}, {"Machine", type text}, {"EOP1", type number}, {"EOP2", type number}, {"EOP3", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Process Date", "Area", "Machine"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Machine]), "Machine", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Period"}})
in
#"Renamed Columns"

If this helps then mark it as a solution.
Thank you.

Sample output: