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.
hello All,
I have a sample data which i have created using power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnYKNVTSUXLxBZEBEDIIRPr7AYmg1DKFgKL8rNTkEiAPJOyoFKuDocsIrAtE+ru5YdXmhKoNrMHXGKzNBGaZc35xCZo2Z2zaTMDaTOG2YdHnohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SBU = _t, DM = _t, PM = _t, PROJECT = _t, #"ON/OFF" = _t, #"Project Profile" = _t, FTE = _t, EmpName = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SBU", type text}, {"DM", type text}, {"PM", type text}, {"PROJECT", type text}, {"ON/OFF", type text}, {"Project Profile", type text}, {"FTE", type number}, {"EmpName", type text}}) in #"Changed Type"
Now i would like to transpose this table as below image using power query or dax.
The image i have attached is, matrix visual view with same data.
But i would like to transofrm the master table as below image using power query or dax.
Any Help Please.
Mohan V.
Solved! Go to Solution.
hi, @Anonymous
Based on my research, I'm afraid it couldn't achieve in Power BI for now.
You want the same view in trnasoformed table as the above view. but it is only the visual view. It is not the data table format.
And if you could use Pivot columns function as below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnYKNVTSUXLxBZEBEDIIRPr7AYmg1DKFgKL8rNTkEiAPJOyoFKuDocsIrAtE+ru5YdXmhKoNrMHXGKzNBGaZc35xCZo2Z2zaTMDaTOG2YdHnohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SBU = _t, DM = _t, PM = _t, PROJECT = _t, #"ON/OFF" = _t, #"Project Profile" = _t, FTE = _t, EmpName = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SBU", type text}, {"DM", type text}, {"PM", type text}, {"PROJECT", type text}, {"ON/OFF", type text}, {"Project Profile", type text}, {"FTE", type number}, {"EmpName", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Project Profile]&[#"ON/OFF"]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ON/OFF", "Project Profile", "EmpName"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "FTE", List.Sum) in #"Pivoted Column"
Result:
Best Regards,
Lin
hi, @Anonymous
Based on my research, I'm afraid it couldn't achieve in Power BI for now.
You want the same view in trnasoformed table as the above view. but it is only the visual view. It is not the data table format.
And if you could use Pivot columns function as below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnYKNVTSUXLxBZEBEDIIRPr7AYmg1DKFgKL8rNTkEiAPJOyoFKuDocsIrAtE+ru5YdXmhKoNrMHXGKzNBGaZc35xCZo2Z2zaTMDaTOG2YdHnohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SBU = _t, DM = _t, PM = _t, PROJECT = _t, #"ON/OFF" = _t, #"Project Profile" = _t, FTE = _t, EmpName = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SBU", type text}, {"DM", type text}, {"PM", type text}, {"PROJECT", type text}, {"ON/OFF", type text}, {"Project Profile", type text}, {"FTE", type number}, {"EmpName", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Project Profile]&[#"ON/OFF"]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ON/OFF", "Project Profile", "EmpName"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "FTE", List.Sum) in #"Pivoted Column"
Result:
Best Regards,
Lin
Thanks for the reply @v-lili6-msft .
I already did this power query thing.
I was looking for a ans that, whether is there any possiblity using power query or dax.
Thanks for confirming that, it couldnt be done.
Regards,
MohanV
hi, @Anonymous
That is not the format of data table, it couldn't be done.
Could you please tell me if you still have other problem? If it not, could you please mark the helpful replies as Answered?
Best Regards,
Lin
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |