Using a list from an excel sheet to filter a lookup

Krays23 · October 9, 2019, 10:37am

HI,

I have been struggling to solve this for a while, i know its simple but i cant get it.

I have a look up table called Material masters lookup that holds all our product numbers in i use this to filter against most things in my model. However a member of staff gave me a specific list of materials they would like me to run a report on in my model.

How do i use the excel sheet (Specific) she gave me to filter my Material master lookup table so i can just search on those materials she wants? Yes i could just not use the MM sheet but this contains other data that i need so i need to have her sheet filter the MM sheet.

MM on the picture is my main material master lookup sheet.
Specific on the picture is the ladies specific products.

Melissa · October 9, 2019, 12:17pm

Hi Krays23,

I needed to identify items listed in a separate list as well and solved it in Power Query as follows:

Load this specific list of materials in Power Query (you have already done so), you can disable “Enable load”. Now you only need one column with your ItemKey and ReferenceThisQueryName later
Then added this code in the Advanced Editor of your Material masters table

 TableBufferListOfMaterials = Table.Buffer(ReferenceThisQueryName),
 #"Added ListOfMaterials" = Table.AddColumn(ReferenceQueryStepBeforeTableBufferHere, "Item in List of Materials", each Table.Contains( TableBufferListOfMaterials , [ItemKey= [ItemKey]]), type logical)

Note that both your tables need to have an equivalent to the ItemKey column
And in Power Query you usualy reference the previous step but where is says ReferenceQueryStepBeforeTableBufferHere you need to go one step further back see the Applied Steps list. The Table.Buffer step was just added to improved the query speed.

The result will be a new column, named [Item in List of Materials] in your Material master table which you can use as Boolean filter in your Report.

BrianJ · October 9, 2019, 12:17pm

@Krays23,

I’m sure there are a lot of different ways to do this, but my recommendation would be to use the DAX IN function. You’ve currently got a nice clean data model, so I would be hesitant to add the “Specific” table as a snowflake off your MM lookup table. Instead, you could keep “Specific” as a disconnected supporting table and use the IN function to filter your report by checking whether the values in your MM table are found in the “Specific” table or not.

Here’s a great explanation of the IN function:

See how this works for you.

Brian

Krays23 · October 9, 2019, 12:22pm

Cheers Brian ill take a look at that.

Im not completely sure how but the way i have posted it originally seems to work actually if i simply drag in the MM material numbers its only showing the associated material that the lady was asking for! im not to sure how that has worked but it seems to have!!!

BrianJ · October 9, 2019, 12:39pm

@Krays23,

Resolution isn’t good enough to see the field details on your data model screenshot, but I think the way you’ve currently got it set up is that the Specific table is filtering the MM table, which in turn is filtering your fact tables. That works fine for this particular issue, but there are likely to be a lot of instances where you won’t want this filtering to occur, which is going to make your DAX more complicated. Best in my opinion to keep “Specific” out of the main model, and just invoke it is needed.

If for some reason you do want to put it in the main model, I would make the relationship with MM inactive, and just use the USERELATIONSHIP function when you need to activate it.

Brian

Krays23 · October 10, 2019, 7:07am

HI Mellissa

I got it working in power query however it takes too long to load into the model over 10 mins the wheel is spinning so i cancelled it

let
Source = ZMDispodat,
#“Removed Duplicates” = Table.Distinct(Source, {“Material Number”}),
#“Reordered Columns” = Table.ReorderColumns(#“Removed Duplicates”,{“Material Number”, “Product Hierarchy”, “Material Description”, “Material Group”, “Material Type”, “Unit of Measure”, “Purchasing Group Number”, “ABC Indicator”, “MRP Type”, “MRP Controller Number”, “Planned Delivery Time”, “GR Processing Time”, “In House Production Time”, “Safety Time”, “Lot Type”, “Safety Stock”, “Reorder Point”, “Maximum Stock Level”, “Fixed Lot Size”, “Minimum Remainig Shelf Life”, “Total Shelf Life”, “Batch Management”, “Old Material Number”, “Planning Strategy Group”, “Created On”, “Created By”, “Last Change”, “Changed By”}),
#“Removed Other Columns” = Table.SelectColumns(#“Reordered Columns”,{“Material Number”, “Product Hierarchy”, “Material Description”, “Material Group”, “Material Type”, “Unit of Measure”, “Purchasing Group Number”, “ABC Indicator”, “MRP Type”, “MRP Controller Number”, “Lot Type”, “Safety Stock”, “Reorder Point”, “Maximum Stock Level”, “Batch Management”, “Planning Strategy Group”}),
#“Inserted Merged Column” = Table.AddColumn(#“Removed Other Columns”, “Merged”, each Text.Combine({[Material Number], " - ", [Material Description]}), type text),
#“Renamed Columns” = Table.RenameColumns(#“Inserted Merged Column”,{{“Merged”, “Material Number & Name”}}),
#“Merged Queries” = Table.NestedJoin(#“Renamed Columns”, {“Product Hierarchy”}, #“Project Codes (Dan)”, {“Project Code”}, “Project Codes (Dan)”, JoinKind.LeftOuter),
#“Expanded Project Codes (Dan)” = Table.ExpandTableColumn(#“Merged Queries”, “Project Codes (Dan)”, {“Project Name”, “Project Code & Name”}, {“Project Codes (Dan).Project Name”, “Project Codes (Dan).Project Code & Name”}),
#“Renamed Columns1” = Table.RenameColumns(#“Expanded Project Codes (Dan)”,{{“Project Codes (Dan).Project Name”, “Project Name”}, {“Project Codes (Dan).Project Code & Name”, “Project Code & Name”}}),
#“Replaced Value” = Table.ReplaceValue(#“Renamed Columns1”,null,“No product hierarachy set”,Replacer.ReplaceValue,{“Project Name”, “Project Code & Name”}),
#“Merged Queries1” = Table.NestedJoin(#“Replaced Value”, {“Material Number”}, #“Price Lookup (MM60)”, {“Material Number”}, “Price Lookup (MM60)”, JoinKind.LeftOuter),
#“Expanded Price Lookup (MM60)” = Table.ExpandTableColumn(#“Merged Queries1”, “Price Lookup (MM60)”, {“Converted Price”}, {“Converted Price”}),
#“Replaced Value1” = Table.ReplaceValue(#“Expanded Price Lookup (MM60)”,null,0,Replacer.ReplaceValue,{“Converted Price”}),
TableBufferListOfMaterials = Table.Buffer(#“Bom Materials (Anca & MM60 Merge)”),
#“Added ListOfMaterials” = Table.AddColumn(#“Expanded Price Lookup (MM60)”, “Item in List of Materials”, each Table.Contains( TableBufferListOfMaterials , [ItemKey= [ItemKey]]), type logical)
in
#“Added ListOfMaterials”

Melissa · October 10, 2019, 11:30am

@Krays23,

I think there are some issues with your last step:
#“Added ListOfMaterials” = Table.AddColumn(#“Expanded Price Lookup (MM60)”, “Item in List of Materials”, each Table.Contains( TableBufferListOfMaterials , [ItemKey= [ItemKey]]), type logical)

I believe your [Material Number] is the equivalent of the field I had called [ItemKey] if so you’d need to substitute that, since you haven’t supplied code for the query: #“Bom Materials (Anca & MM60 Merge)” please check the fieldname there as well. I have assumed these fields are named exactly the same.
In the final step you don’t reference #“Replaced Value1” but the step before that, which means #“Replaced Value1” won’t be applied at all.

I’ve tried adjusting it, although that’s a bit more tricky outside the Advanced Editor and without being able to see the applied steps but I believe it’s okay

let
Source = ZMDispodat,
#“Removed Duplicates” = Table.Distinct(Source, {“Material Number”}),
#“Reordered Columns” = Table.ReorderColumns(#“Removed Duplicates”,{“Material Number”, “Product Hierarchy”, “Material Description”, “Material Group”, “Material Type”, “Unit of Measure”, “Purchasing Group Number”, “ABC Indicator”, “MRP Type”, “MRP Controller Number”, “Planned Delivery Time”, “GR Processing Time”, “In House Production Time”, “Safety Time”, “Lot Type”, “Safety Stock”, “Reorder Point”, “Maximum Stock Level”, “Fixed Lot Size”, “Minimum Remainig Shelf Life”, “Total Shelf Life”, “Batch Management”, “Old Material Number”, “Planning Strategy Group”, “Created On”, “Created By”, “Last Change”, “Changed By”}),
#“Removed Other Columns” = Table.SelectColumns(#“Reordered Columns”,{“Material Number”, “Product Hierarchy”, “Material Description”, “Material Group”, “Material Type”, “Unit of Measure”, “Purchasing Group Number”, “ABC Indicator”, “MRP Type”, “MRP Controller Number”, “Lot Type”, “Safety Stock”, “Reorder Point”, “Maximum Stock Level”, “Batch Management”, “Planning Strategy Group”}),
TableBufferListOfMaterials = Table.Buffer(#“Bom Materials (Anca & MM60 Merge)”),
#“Added ListOfMaterials” = Table.AddColumn(#“Removed Other Columns”, “Item in List of Materials”, each Table.Contains( TableBufferListOfMaterials , [Material Number= [Material Number]]), type logical),
#“Inserted Merged Column” = Table.AddColumn(#“Removed Other Columns”, “Merged”, each Text.Combine({[Material Number], " - ", [Material Description]}), type text),
#“Renamed Columns” = Table.RenameColumns(#“Inserted Merged Column”,{{“Merged”, “Material Number & Name”}}),
#“Merged Queries” = Table.NestedJoin(#“Renamed Columns”, {“Product Hierarchy”}, #“Project Codes (Dan)”, {“Project Code”}, “Project Codes (Dan)”, JoinKind.LeftOuter),
#“Expanded Project Codes (Dan)” = Table.ExpandTableColumn(#“Merged Queries”, “Project Codes (Dan)”, {“Project Name”, “Project Code & Name”}, {“Project Codes (Dan).Project Name”, “Project Codes (Dan).Project Code & Name”}),
#“Renamed Columns1” = Table.RenameColumns(#“Expanded Project Codes (Dan)”,{{“Project Codes (Dan).Project Name”, “Project Name”}, {“Project Codes (Dan).Project Code & Name”, “Project Code & Name”}}),
#“Replaced Value” = Table.ReplaceValue(#“Renamed Columns1”,null,“No product hierarachy set”,Replacer.ReplaceValue,{“Project Name”, “Project Code & Name”}),
#“Merged Queries1” = Table.NestedJoin(#“Replaced Value”, {“Material Number”}, #“Price Lookup (MM60)”, {“Material Number”}, “Price Lookup (MM60)”, JoinKind.LeftOuter),
#“Expanded Price Lookup (MM60)” = Table.ExpandTableColumn(#“Merged Queries1”, “Price Lookup (MM60)”, {“Converted Price”}, {“Converted Price”}),
#“Replaced Value1” = Table.ReplaceValue(#“Expanded Price Lookup (MM60)”,null,0,Replacer.ReplaceValue,{“Converted Price”})
in
#“Replaced Value1”

Let me know if this helped