Forum Discussion

Ahmedx's avatar
Ahmedx
Super User
2 years ago

pls try this

let
    Source = Excel.Workbook(File.Contents("C:\Users\User\Desktop\Data Transformation in Power Bi.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project Name", type text}, {"Qty", Int64.Type}, {"Cat", type text}, {"Dispatch Date", type any}, {"FAT Date", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Project Name", "Qty"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Project Name", "Qty", "Cat"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Attribute", "Cat"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"