In this example, a business team maintains a list of employee IDs in an Excel workbook and wants the ability to access several columns from the employee dimension table in the data model related to these IDs.
- Create an M query that retrieves the employee IDs from the Excel workbook:
let Source = Excel.Workbook(File.Contents("J:\Finance\TeamFiles\EmployeeIDs.xlsx"), null, true),ExcelTable = Source{[Item="EmployeeTbl",Kind="Table"]}[Data],TypeConversion = Table.TransformColumnTypes(ExcelTable,{{"Employee Alternate Key", type text}}),RemoveNullsAndDuplicates = Table.Distinct(Table.SelectRows(TypeConversion, each [Employee Alternate Key] <> null))in RemoveNullsAndDuplicates
- The employee IDs are stored in an Excel table object ...
