Search code examples
powerbipowerquerym

Combine PDF where target information in different position

I have several PDF file need to combine and get one target information from each file.

Target information is a date.

I can only locate the target information in a specific line (now each file has one row), but no idea how to located it in which column? The only thing I can be sure of is that "target name" is all the same in every file and "target info" is just behind it.

single data

summary data

I think I can convert it from a row to a column, and then change the column into date format, so that other data will be error, and the only correct information can be filtered out. That's wired, how to get the index and use index to select true columns to get the correct data as shown below. final

Solution

  • In powerquery,

    if you had a whole table already populated as in your example

    enter image description here

    Then to get target name (next to last non-null column) , add column, custom column with formula

    =  List.LastN(List.RemoveNulls(Record.ToList(_)),2){0}

    Then add column, custom column, with same formula, which now picks up the target info (last non-null column)

    = List.LastN(List.RemoveNulls(Record.ToList(_)),2){0}

    then click select those two columns and the first column, right click, remove other columns

    The formula converts the row to a list, removes nulls, and takes last 2 items, which is then selected with {x}

    If you just have a table with a single row, enter image description here

    you could use the same method as above, or just grab rightmost item with

    x = List.Last(List.RemoveNulls(Record.ToList(Source{0})))

    If you wanted to do something like find the cell next to the cell containing "xxx" then you could add custom column with

    = Record.ToList(_){List.PositionOf(Record.ToList(_),"xxx")+1}