Power Query - how to Find columns in a range

[sara121]

Hi Everyone,

I'm trying to transform a table converted from PDF file. The columns converted varies in numbers.
I want to keep columns between "Debit" and "Date" only.
What function can I use to select the range?
so I can remove other columns dynamically.
Thanks a lot.

 

[alansidman]

Why not just use the Columns Icon on the Home Tab and remove the unwanted columns. Can also select columns to keep and remove others with the same Icon.

 

[sara121]

Because in the next file uploaded, the range could be just "DEBIT" and "DATE" only. or the column number between the "DEBIT" and "DATE" may be changed as well, like "DEBIT", "Column 10", "DATE".

 

[alansidman]

I don't see a means to the end. Good Luck and hope someone else can see it clear.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    p1 = List.PositionOf(tcn, "DEBIT"),
    p2 = List.PositionOf(tcn, "DATE"),
    Result = Table.SelectColumns(Source, List.Range(tcn, p1, p2-p1+1))
in
    Result

 

[smozgur]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    p1 = List.PositionOf(tcn, "DEBIT"),
    p2 = List.PositionOf(tcn, "DATE"),
    Result = Table.SelectColumns(Source, List.Range(tcn, p1, p2-p1+1))
in
    Result

Perfect!

 

[sara121]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    p1 = List.PositionOf(tcn, "DEBIT"),
    p2 = List.PositionOf(tcn, "DATE"),
    Result = Table.SelectColumns(Source, List.Range(tcn, p1, p2-p1+1))
in
    Result

wow.. works like a charm!! Thanks so much!!