Table with distinct values from other tables

Power Query
1

Hi Community,

I am after some Power Query M code.

I have 3 imported tables, each having a SiteID, SiteName field in along with other fields. Each table has different fields apart from SiteID and SiteName.
I want to have another table that uniquely lists the SiteID, SiteName based from the values of SiteID and SIteName from the above 3 tables. The 3 tables have repeating SiteID, SiteNames in.
I seem to be able to do it with 1 table, but need to include the unique SiteIDs and SiteNames from all the tables.

2

Hi @marcster_uk,

Try this custom function, supply it a list with your 3 tables and a list with column keys: { “SiteID”, “SiteName” } to return a table with distinct combinations.

( tables as list, colKeys as list ) as table =>
let
    selectCols = List.Transform( tables, each Table.SelectColumns( _, colKeys, MissingField.UseNull )),
    distinctCombination = Table.Distinct( Table.Combine( selectCols ))
in
    distinctCombination

I didn’t test this, let me know if you encounter any issues
Hope this is helpful

1 Like