List.Union
Combines several lists into one list of the values that appear in any of them, with duplicates removed.
List.Union(lists, optional equationCriteria)Returns: A list of unique values.
What it does
List.Union takes a list of lists and returns every value that appears in at least one of them, once each. Use it to build a master list from several sources, such as the vendors from two systems, without listing a vendor twice.
Syntax and arguments
lists: a list of lists, for example{ {...}, {...} }.equationCriteria(optional): how values are compared, such asComparer.OrdinalIgnoreCase.
Example
Two systems each have a list of active customer codes. The combined list should contain each code once.
let
SystemA = {"C-100", "C-200", "C-300"},
SystemB = {"C-200", "C-400"},
Master = List.Union({SystemA, SystemB})
in
Master
Master is {"C-100", "C-200", "C-300", "C-400"}. The code "C-200" appears in both sources and is kept once.
Example: a second, different use
A combined list of account codes from two charts of accounts, to find all codes that need a mapping.
Things to watch
- Union removes duplicates, so the count of the result is not the count of the rows in the sources. Count each source before combining.
- Keep the source of each value in mind. The union does not say which list a value came from. Use List.Intersect or List.Difference to find out.
- Capital letters matter by default. Pass Comparer.OrdinalIgnoreCase for names typed by people.
- For combining tables with their columns, use Table.Combine and then remove duplicates.
In business terms
Master lists of vendors, customers or accounts built from several systems, and the complete set of codes for a mapping exercise, are common uses. A union with a clear count before and after makes the merge easy to check.