Forum Discussion

Ocasio27's avatar
Ocasio27
Iron Contributor
May 27, 2020
Solved

Creating a powerquery to fix a table

I am trying to make a query step to change the left example to the right example. The problem is that the client keeps sending them table with new rows that have updated results rather than just upda...
BI & Data Analysis
excel
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    May 27, 2020

    Ocasio27 

    Not sure there is the source data - in another file or in Table/range of the current file. In general that's doesn't matter, just to build the sample

    Let select columns from A to E and name selection as Range. Query it.

    Add index column to the table.

    Sort on first column to combine same ID:s together, right after that sort on Index in descending order to keep the latest records first.

    Add one more index column to fix result in memory (or wrap previous step with Table.Buffer() ).

    Select first column, Remove duplicates.

    Remove bot columns with indexes.

    Load result back into the sheet.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 27, 2020

Ocasio27 

Not sure there is the source data - in another file or in Table/range of the current file. In general that's doesn't matter, just to build the sample

Let select columns from A to E and name selection as Range. Query it.

Add index column to the table.

Sort on first column to combine same ID:s together, right after that sort on Index in descending order to keep the latest records first.

Add one more index column to fix result in memory (or wrap previous step with Table.Buffer() ).

Select first column, Remove duplicates.

Remove bot columns with indexes.

Load result back into the sheet.

Resources