Forum Discussion
Solved
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. Exa...
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.
dufoq3
Community Champion
Community ChampionHi 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
RemovedNullColumns