Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |