Forum Discussion
Solved
Transform data with 2 different headers
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.
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"
3 Replies
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"Here it is. Just change the path to the file.
