Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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:
It's works, thanks!👍
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:
Hi @QZ ,
You can do this with Unpivot option in Power Query.
Try this and let me know if you have any queries.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.