Forum Discussion
Power Query or DAX: Need help in table transforming
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.
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
3 Replies
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