Columns association in query editor

[Anonymous]

Hi all,

I'm facing an issue in the query editor.

On the above screenshot, the referents in the referent column are associated with the IDs of the id column. And the measurements of the measures column are associated with the IDs of the id.1 column.

IDs from both id columns are the same but not in the same order.

My wish is to have the referents associated with the ids of the id.1 column, without using separate tables and then merge tables (because I'm restricted by API limitations).

How would you do that ?

Thanks in advance,

Cado

[Cado_one · Accepted solution]

After some work on it, a solution was to add a custom column taking for value 1 if id.1=id else 0 and then filter on it to keep only the 1.

[amitchandak]

@Anonymous , Copy this table, remove the first two-column, Remove duplicates for the remaining two-column  this will be table2

From Table1 , remove columns 3,4, if needed remove duplicates.

Merge these two tables now by matching id1. and id

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Anonymous]

Hi @amitchandak 

As I said in the first message I can't duplicate the table because it will do extra queries and the API limitations don't allow this.

I can't reference neither because their are subsequent steps that delete the IDs columns and more over, references are forbiden for the automatic refresh in the Power BI Service.

Is there an other way to proceed ?