Forum Discussion
Max_Mustermann
Aug 20, 2024Copper Contributor
Power Query losing Columns
Hello there, I have a problem importing some folders into power query. The interesting part is the different files in said folder seem to have a varying amount of columns - and power query onl...
- Aug 20, 2024
In case of csv check Transform Sample File query, first step looks like
= Csv.Document(Parameter1,[Delimiter=";", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])Change Columns=10 on Columns=20 or like, i.e. max possible number of columns.
When in combined file remove all columns with no data. As variant that could be function which removes all empty rows and columns as
(Source as table) as table => let RemoveEmptyColumns = Table.SelectColumns( Source, List.Select( Table.ColumnNames(Source), each List.NonNullCount(Table.Column(Source,_)) <> 0 ) ), RemoveEmptyRows = Table.SelectRows( RemoveEmptyColumns, each not List.IsEmpty(List.RemoveMatchingItems( Record.FieldValues(_), {"", null} ) ) ) in RemoveEmptyRowsNot sure about PDF, check the connector options if something similar.
SergeiBaklan
Aug 20, 2024Diamond Contributor
In case of csv check Transform Sample File query, first step looks like
= Csv.Document(Parameter1,[Delimiter=";", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])
Change Columns=10 on Columns=20 or like, i.e. max possible number of columns.
When in combined file remove all columns with no data. As variant that could be function which removes all empty rows and columns as
(Source as table) as table =>
let
RemoveEmptyColumns = Table.SelectColumns(
Source,
List.Select(
Table.ColumnNames(Source),
each List.NonNullCount(Table.Column(Source,_)) <> 0
)
),
RemoveEmptyRows = Table.SelectRows(
RemoveEmptyColumns,
each not List.IsEmpty(List.RemoveMatchingItems( Record.FieldValues(_), {"", null} ) )
)
in
RemoveEmptyRows
Not sure about PDF, check the connector options if something similar.