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.
Currently my data are being stored as 2 seperate line as sample below.
How can I move the ID from row2 to new column so that it become single line?
Thank you.
Solved! Go to Solution.
Hello @khteng79
This is the required M-code to achieve the same requirement.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDOzy1IzKtU0lHyDXZSCAgHMpz93dxcXRV8gczgEEc/F1dXIMvAWMHJNcw1yNEdxDM0NjY0MTG1NDUxNLA0MjU2NFCK1aGigabmQANjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer_Name = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t, ID = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Transposed Table" = Table.Transpose(#"Unpivoted Columns"),
#"Removed Columns" = Table.RemoveColumns(#"Transposed Table",{"Column7", "Column8", "Column9", "Column10", "Column11"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer_Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}, {"ID", type text}, {"ID_1", type text}})
in
#"Changed Type"
Thank you.
If my answer helps you, please mark it as solution.
Pivot the table to obtain the value.
Is this what you want?
Thank you.
Thanks for the info.
But I'm looking to pivot the ID from 2 row into 1 row, where Company Nae, Q1, Q2, Q3, Q4 are same.
I am sorry I am not getting the requirement.
Could you please help me with expected output screenshot using Excel.
Thank you.
Here you go on Current & To BE:
Hello @khteng79
This is the required M-code to achieve the same requirement.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDOzy1IzKtU0lHyDXZSCAgHMpz93dxcXRV8gczgEEc/F1dXIMvAWMHJNcw1yNEdxDM0NjY0MTG1NDUxNLA0MjU2NFCK1aGigabmQANjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer_Name = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t, ID = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Transposed Table" = Table.Transpose(#"Unpivoted Columns"),
#"Removed Columns" = Table.RemoveColumns(#"Transposed Table",{"Column7", "Column8", "Column9", "Column10", "Column11"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer_Name", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}, {"ID", type text}, {"ID_1", type text}})
in
#"Changed Type"
Thank you.
If my answer helps you, please mark it as solution.
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.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |