Forum Discussion
Solved
Transpose or Unpivot
Hello, I have a file Co Name, Amount and Date are all in separate columns. I want: Date to be transposed to rows but keep the Co Name and Amount in rows But Group co name. I am not sur...
try this (my) last guess!
is this what you are after?
Hi Rocco, I think that file is for Power BI (.pbix)? I do not have that software. Would you possibly be able to save it as an excel file? thank you.
let Source = Excel.Workbook(File.Contents("C:\Users\sprmn\OneDrive\Documents\Power BI Desktop\FCall.xlsx"), null, true), FCall_20220325_DefinedName = Source{[Item="FCall_20220325",Kind="DefinedName"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(FCall_20220325_DefinedName, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LCID", Int64.Type}, {"dte", type date}, {"Amt", type text}, {"ProjName", type text}, {"CompanyName", type text}, {"LCNo", type text}, {"ProjID", Int64.Type}, {"LCName", type text}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"dte", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"dte", type text}}, "it-IT")[dte]), "dte", "Amt"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"LCID", "ProjName", "LCNo", "ProjID", "LCName"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"CompanyName", Order.Ascending}}) in #"Sorted Rows"try this (my) last guess!
Hi Rocco, yes thats it. thank you. REally appreciate it. I am a novice user of PQ.
