VLOOKUP to 36 Different Tables: Power Query Transforms 36 Tables Into 1
Learn how to use Power Query transform 36 Lookup Tables Into 1 Lookup Table for VLOOKUP Formula.
(00:10) Introduction to problem we want to solve and look back to Excel Magic Trick 1231.
(00:13) Use Filter Feature to get 36 Tables into Power Query
(02:21) Name Query to we can use Name in VLOOKUP formula later.
(02:35) Use Split Columns feature with Two Delimiters (Colon and Space) to remove “Table” from Field Name and create the “Product” column.
(03:12) Use Split Columns feature with One Delimiter to Split “Region” and “Product Group” columns.
(03:32) Use Fill Down Feature to fill “Region” and “Product Group” throughout columns
(03:49) Filter out Nulls in Discount Column to remove unwanted records.
(04:22) Merge Columns into one Unique Identifier Column so VLOOKUP can have a first column that it can use to lookup the discount rate. See how to select the columns in the correct order so the Merge Columns feature merges in the correct order.
(05:25) Rename columns
(05:52) Close and Load to Excel as a Table.
(06:32) VLOOKUP with Joined lookup_value to get the correct discount rate from 36 tables.