Forum Discussion
Handling Missing Columns in Combined Files to Maintain Header Alignment
When combining six files for example, sometimes one or a few of the files will have one column (titled "YYY") with no data which causes the column to not report in that file and headers to shift. Example, files 1-3 and 5 have these three columns with data: "XXX", "YYY" and "ZZZ". Then files 4 and 6 are missing column "YYY", so column "ZZZ" is shifted next to column "XXX".
| Example | |||
| Col. A (file name) | Col. L | Col. M | Col. N |
| …1.txt | XXX | YYY | ZZZ |
| …2.txt | XXX | YYY | ZZZ |
| …3.txt | XXX | YYY | ZZZ |
| …4.txt | XXX | ZZZ | |
| …5.txt | XXX | YYY | ZZZ |
| …6.txt | XXX | ZZZ |
Is there a way to look at the first row in column A ending in "...1.txt", and check if column M has "YYY" as the header, if yes do nothing and if no then shift every column from column M - W over one column to the right for all rows containing "...1.txt" in column A. This would be repeated for all other file names through "...6.txt".
Thanks for any help in advanced!
In Power BI, you can use Power Query to handle this issue:
- Load Data: Load your combined file into Power Query.
- Identify Misalignment:
- Add a conditional column: = if Text.EndsWith([Col. A], "1.txt") and [Col. M] <> "YYY" then true else false.
- Shift Columns:
- Duplicate your table.
- Shift columns M-W for misaligned rows using a custom column and transformation logic.
- Combine Data:
- Append the corrected table back to the original, ensuring all rows align.
7 Replies
In Power BI, you can use Power Query to handle this issue:
- Load Data: Load your combined file into Power Query.
- Identify Misalignment:
- Add a conditional column: = if Text.EndsWith([Col. A], "1.txt") and [Col. M] <> "YYY" then true else false.
- Shift Columns:
- Duplicate your table.
- Shift columns M-W for misaligned rows using a custom column and transformation logic.
- Combine Data:
- Append the corrected table back to the original, ensuring all rows align.
Hi EC305 ,
Can you provide examples of actual source data please, one where the column is present, and one where it is not? Please also provide an example of what your desired outcome is based on these two new examples provided.
I think I understand the issue, but the question is a bit too vague to be sure.
For more info on how to provide good data for us to work with, please read through this:
Pete
Hi EC305, check this:
Output
let Data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WetSwzFCvpKJESUcpIiICSEZGRgLJqKgopVgdsLQRfmlj/NImKNIgCR0lBZikKX69Ztj1xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col. A (file name)" = _t, #"Col. L" = _t, #"Col. M" = _t, #"Col. N" = _t]), ReplacedValue = Table.ReplaceValue(Data," ",null,Replacer.ReplaceValue,{"Col. N"}), Source = Table.Group(ReplacedValue, {"Col. A (file name)"}, {{"Data", each _, type table}}, 0), Ad_Shifted = Table.AddColumn(Source, "Shifted", each [ a = [Data], b = List.RemoveMatchingItems(Table.ColumnNames(a), {"Col. M"}), c = if Table.First(a)[#"Col. M"] = "YYY" then Table.RenameColumns(Table.SelectColumns(a, b), List.Zip({ b, List.RemoveLastN(Table.ColumnNames(a)) })) else a ][c], type table), CombinedShifted = Table.Combine(Ad_Shifted[Shifted]), RemovedNullColumns = [ a = Table.PromoteHeaders(Table.DemoteHeaders(Table.Profile(CombinedShifted))), b = Table.SelectRows(a, each [NullCount] <> [Count])[Column], c = Table.SelectColumns(CombinedShifted, b) ][c] in RemovedNullColumnsIn power Query by appending the queris, the table will append based on the column name not the order of columns, so no problem in your case also.
but if you insiste on your case, I recomend to create a blank table including all the six columns, and then use it as the first table in the appending process.Hi EC305 ,
Just wanted to check if the query has been resolved at your end or if any of the solutions provided met your requirements. If so, please mark the helpful reply as the solution to help others in the community with similar queries.
If further assistance is needed, please reach out.
Thank you.Hi EC305 ,
We just wanted to check in again regarding your issue. If you've found a solution, marking the reply as the solution and leaving a kudos would be greatly appreciated, it helps the community and others with similar questions.
If you're still facing challenges or have further questions, please reach out.
Thank you.Hi EC305 ,
May i ask if you have resolved your query. If yes, please mark the helpful reply as 'Accespt as Solution' to assist others with similar queries. If you still need assistance please reach out.
Thank you.
