Forum Discussion
Solved
combining Excel files with dfferent columns
Hello! I am trying to combine Excel files in Powerquery that have different columns: The year will always change, and that bit I was able to somehow capture dynamically based on the column position...
Hello - yes, this is possible. You would just change the portion of the script that removes columns to only remove those that have duplicates, not to remove the empty columns.
BEFORE
// Combine duplicate plan columns and generic columns into one list. ColumnNamesToRemove = List.Combine ( { DuplicatePlanColumns, GenericColumnNames } ), // -------------------------------------------------------- // Clean up the column names. // -------------------------------------------------------- // Remove specific columns from the source table. RemoveColumns = Table.RemoveColumns ( Source, ColumnNamesToRemove ),AFTER
// Combine duplicate plan columns and generic columns into one list. // ColumnNamesToRemove = List.Combine ( { DuplicatePlanColumns, GenericColumnNames } ), // -------------------------------------------------------- // Clean up the column names. // -------------------------------------------------------- // Remove specific columns from the source table. RemoveColumns = Table.RemoveColumns ( Source, DuplicatePlanColumns),
Hello, Valeria
I'd create and manually maintain some kind of mapping table like this
YR2022 == YR2023 == Combined_report
2022 PLAN == 2023 PLAN == This YR PLAN
2023 PLAN == 2024 PLAN == Next YR PLAN
and so on. Then rename columns of your tables in PQ with Table.RenameColumns function. And finally Table.Combine your tables with new column names.
2022_map = List.Zip({map_tbl[YR2022], map_tbl[Combined_report]}),
2023_map = List.Zip({map_tbl[YR2023], map_tbl[Combined_report]}),
renamed_2022 = Table.RenameColumns(tbl_2022, 2022_map),
renamed_2023 = Table.RenameColumns(tbl_2023, 2023_map),
combined_report = Table.Combine(renamed_2022, renamed_2023)