Left Joining Tables using Power Query / Measures

[EHa]

Hi All, Sorry for the simplicity of the question - I had a look through a few similar questions but couldn't find any solutions which helped me out.

I understand my tables are not of the easiest set up, but they need to stay in the current format to work with the rest of my report.

This is my simple example: 

 Put simply, I would like the name of the city , instead of the ID, when LevelOfDetail is Region. 

I can't just link up the two tables and use region instead of attribute , as I would also like the attribute column to show for the other levelofdetail values. My two first approaches were to 1) - create a measure with an if function which used the reference to find the city when levelofdetail is city, and just used the existing attribute when not. 2) - write a left join between the two tables in power query ( these two tables will be import from sql) , and then sort the tables to the correct form using the toolbars in power query.

I couldn't get either to work, so if anyone has the solyution, that would be greatly appreciated.

Thanks a lot,

EHa

[v-lili6-msft · Accepted solution]

hi  @EHa 

for your case, I suggest you create a new attribute column in table2 by this logic:

New Attribute = 
VAR _ID=IF('Table 2'[LevelofDetail]="Region",VALUE('Table 2'[Attribute])) RETURN
IF('Table 2'[LevelofDetail]="Region",LOOKUPVALUE('Table 1'[City],'Table 1'[ID],_ID),'Table 2'[Attribute])

Now when create a visual, use this new attribute.

and here is sample pbix file, please try it.

Regards,

Lin

[EHa]

Hi Lin,

That's great, thanks a lot.

Appreciate the effort in remaking it too, very clear now.

Cheers,

EHa

@v-lili6-msft