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!
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"