How to deal with misaligned columns that share column headers?

[YR26]

Currently dealing with a project that requires me to compile excel files into a power BI report. However, some of the excel files have empty columns. Rather than returning 0s, the columns are not even returned by the program we are using to pull this data. Misaligned columns in PQ.

How do I go about solving this?

All the columns have named column headers. Is there a way to append the tables when combining the data? Maybe sort the info by column header rather than column location?

I was playing around a bit and got my hands on all possible column header names, put them into a table, and appended the query to my Transform Sample File. It worked on just that file and kept only unique headers, but I don't feel like I made any headways.

[v-jingzhan-msft]

Hi @YR26 

@AlienSx 's method should work. Have you tried that? 

In addition, your idea to append the query (which has all possible column header names) to Transform Sample File query should work. It has the same logic. Where did you think it doen's work? You can add a step to append them easily. This will be applied to all files combined. 

Best Regards,
Jing

[AlienSx · Accepted solution]

don't get your problem because Table.Combine combines tables just fine using correct column names and does not care about missing columns. If you need to make sure that table goes with full list of columns to Power BI then maybe you need to create a blank table template with all the columns listed and include it into your solution?

let
    tbl_template = #table({"col_a", "col_b", "col_c"}, {}),
    tbl_bc = #table({"col_b", "col_c"}, {{1, 2}}),
    tbl_c = #table({"col_c"}, {{1}}),
    result = tbl_template & tbl_bc & tbl_c
in
    result

[lbendlin]

Please read about the difference between Table.RemoveColumns and Table.SelectColumns