Transform data with 2 different headers

[mofu1401]

Hi all, 

I have a data sheet with sales and visits in a single worksheet.

I was trying to transform into a single table but could get it right.

Can someone help me with it. Thank you so much.

FY23 Sales Visit.xlsx

[mofu1401]

@danextian hi sir, can you share the pbix?

Thank you.

[danextian]

Here it is. Just change the path to the file.

[danextian · Accepted solution]

Hi @mofu1401 

Please see this sample query

let
    Source = Excel.Workbook(File.Contents("filepath"), null, true),
    FY23_Sheet = Source{[Item="FY23",Kind="Sheet"]}[Data],
    #"Filtered Rows" = Table.SelectRows(FY23_Sheet, each [Column1] <> null and [Column1] <> ""),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category", each if [Column1] = "Sales" or [Column1] = "Visit" then  [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
    #"Grouped Rows" = let
    Source = #"Filled Down",  // Your source table
    ColumnsList = Table.ColumnNames(Source),
    TypesList = List.Transform(ColumnsList, each if _ = "Category" then "text" else "any"),
    TypeExpression = "type table [" & Text.Combine(List.Transform(List.Zip({ColumnsList, TypesList}), each _{0} & "=" & _{1}), ", ") & "]",
    TypeRecord = Expression.Evaluate(TypeExpression, #shared),
    Grouped = Table.Group(Source, {"Category"}, {{"Grouped", each _, TypeRecord}})
    
in
   Grouped,
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Grouped Transformed", each let 
PromotedHeaders = 
Table.PromoteHeaders([Grouped], [PromoteAllScalars = true]),
AutomaticallyRenamedColumns = Table.RenameColumns(PromotedHeaders, {{Table.ColumnNames(PromotedHeaders){0}, "Customer"}} ),
Unpivoted = Table.UnpivotOtherColumns ( AutomaticallyRenamedColumns, {"Customer"}, "Date", "Value")
in Unpivoted),
    #"Expanded Grouped Transformed" = Table.ExpandTableColumn(#"Added Custom1", "Grouped Transformed", {"Customer", "Date", "Value"}, {"Customer", "Date", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Grouped Transformed",{{"Date", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"Grouped"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Category]), "Category", "Value", List.Sum),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Customer", type text}, {"Sales", type number}, {"Visit", Int64.Type}})
in
    #"Changed Type1"