Power Query - Filter Using Workbook Table

[Caleeco]

Hello,

- I have about 10 data sources which I have Imported/Merged using Power query into 1 table, lets call this qryOUTPUT, and is loaded as 'Only Connection'
- In qryOUTPUT I have field called Part (Text type)
- I want to have a local table in the workbook called tbl_Part_Entry (user entry)
- I am then planning to have a MERGE (inner Join) to filter qryOUTPUT.Part based on tbl_Part_Entry (user entry)

The Problem
What can I put in tbl_Part_Entry, when I want to return all rows of qryOUTPUT? I've tried using "*", but get 0 rows returned as its doing a literal comparison.

Any help is appreciated
Cheers
Caleeco

 

[sandy666]

as example
with value in DV list

Source				DVList		Result
Name	Value	Item		Andy		Name	Value	Item
Andy	6	cup				Andy	6	cup
Alan	3	glass				Andy	4	pen
Mike	11	wanad
Andy	4	pen
Alan	5	knife

without value in DV list

Source				DVList		Result
Name	Value	Item				Name	Value	Item
Andy	6	cup				Andy	6	cup
Alan	3	glass				Alan	3	glass
Mike	11	wanad				Mike	11	wanad
Andy	4	pen				Andy	4	pen
Alan	5	knife				Alan	5	knife

let
    DV = Excel.CurrentWorkbook(){[Name="dv"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Mrg = Table.NestedJoin(DV,{"Column1"},Source,{"Name"},"Source", if DV{0}[Column1] = null then JoinKind.RightAnti else JoinKind.LeftOuter),
    Exp = Table.ExpandTableColumn(Mrg, "Source", {"Item", "Name", "Value"}, {"Item", "Name", "Value"}),
    TSC = Table.SelectColumns(Exp,{"Name", "Value", "Item"})
in
    TSC

if this is what you want, adapt to your needs

 

[Caleeco]

Hi Sandy!

Thanks for getting back to me. Really appreciate you helping me with these PQ problems (i'm a noob! ?)

That looks to be almost exactly what I need! The only difference it the Source is another Query in the workbook.

I'll see if I can modify your code and get it working tomorrow (whilst Im at work).

I'll report back to let you know how I get on!

Thanks
Caleeco

 

[sandy666]

The only difference it the Source is another Query in the workbook.

Do all in that Query or New Blank Query then Source = Query1 (where Query1 is the name of your another Query) but imho all-in-one is a better option
would be fine to see structure of data and M
you can theorize for 20 or more posts and nothing will come of it

 

[Caleeco]

Do all in that Query or New Blank Query then Source = Query1 (where Query1 is the name of your another Query) but imho all-in-one is a better option
would be fine to see structure of data and M
you can theorize for 20 or more posts and nothing will come of it

I haven't really used the Advanced editor before, been sticking to the GUI withing Power Query & Excel, this resulted in many tables of data being created.

I took your advice and re-did it all in one Query! M is shown below, apologies if this is messy!

let
ZQM14 = Table.NestedJoin(ZQM14, {"Vendor"}, tbl_VendorGroup, {"Vendor Code"}, "tbl_VendorGroup", JoinKind.LeftOuter),
#"Expanded tbl_VendorGroup" = Table.ExpandTableColumn(ZQM14, "tbl_VendorGroup", {"Vendor Group"}, {"tbl_VendorGroup.Vendor Group"}),
ZQM14.VG = Table.ReplaceValue(#"Expanded tbl_VendorGroup",null,"Other",Replacer.ReplaceValue,{"tbl_VendorGroup.Vendor Group"}),

Merge.WC = Table.NestedJoin(ZQM14.VG, {"Unit to be insp."}, tbl_WorkCentreTable1, {"Work Centre"}, "tbl_WorkCentreTable1", JoinKind.LeftOuter),
#"Expanded tbl_WorkCentreTable1" = Table.ExpandTableColumn(Merge.WC, "tbl_WorkCentreTable1", {"Work Centre Name"}, {"tbl_WorkCentreTable1.Work Centre Name"}),
ZQM14.WC = Table.ReplaceValue(#"Expanded tbl_WorkCentreTable1",null,"Omitted",Replacer.ReplaceValue,{"tbl_WorkCentreTable1.Work Centre Name"}),

Merge.ZQM12 = Table.NestedJoin(ZQM14.WC, {"Notification"}, ZQM12, {"Notification"}, "ZQM12", JoinKind.LeftOuter),
#"Expanded ZQM12" = Table.ExpandTableColumn(Merge.ZQM12, "ZQM12", {"Reference number"}, {"ZQM12.Reference number"}),
ZQM14.ZQM12 = Table.ReplaceValue(#"Expanded ZQM12",null,"",Replacer.ReplaceValue,{"ZQM12.Reference number"}),

Merge.COOIS = Table.NestedJoin(ZQM14.ZQM12, {"ZQM12.Reference number"}, COOIS, {"Order"}, "COOIS", JoinKind.LeftOuter),
#"Expanded COOIS" = Table.ExpandTableColumn(Merge.COOIS, "COOIS", {"Rework Hours"}, {"COOIS.Rework Hours"}),
ZQM14.COOIS = Table.ReplaceValue(#"Expanded COOIS",null,0,Replacer.ReplaceValue,{"COOIS.Rework Hours"}),

Merge.LTH = Table.NestedJoin(ZQM14.COOIS, {"ZI33 H"}, LongText_Header, {"Object Reference"}, "LongText_Header", JoinKind.LeftOuter),
#"Expanded LongText_Header" = Table.ExpandTableColumn(Merge.LTH, "LongText_Header", {"Header Long Text"}, {"LongText_Header.Header Long Text"}),
ZQM14.LTH = Table.ReplaceValue(#"Expanded LongText_Header",null,"Long Text Missing",Replacer.ReplaceValue,{"LongText_Header.Header Long Text"}),

Merge.LTI = Table.NestedJoin(ZQM14.LTH, {"ZI33 I"}, LongText_Items, {"Object Reference"}, "LongText_Items", JoinKind.LeftOuter),
#"Expanded LongText_Items" = Table.ExpandTableColumn(Merge.LTI, "LongText_Items", {"Items Long Text"}, {"LongText_Items.Items Long Text"}),
ZQM14.LTI = Table.ReplaceValue(#"Expanded LongText_Items",null,"Long Text Missing",Replacer.ReplaceValue,{"LongText_Items.Items Long Text"}),

Merge.LTC = Table.NestedJoin(ZQM14.LTI, {"ZI33 C"}, LongText_Causes, {"Object Reference"}, "LongText_Causes", JoinKind.LeftOuter),
#"Expanded LongText_Causes" = Table.ExpandTableColumn(Merge.LTC, "LongText_Causes", {"Cause Long Text"}, {"LongText_Causes.Cause Long Text"}),
ZQM14.LTC = Table.ReplaceValue(#"Expanded LongText_Causes",null,"Long Text Missing",Replacer.ReplaceValue,{"LongText_Causes.Cause Long Text"})

in
ZQM14.LTC

So I have a Query called 'Material Selection' (imported as a data table into PQ). This list will change as the User will paste in the materials they wish to see data for.

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}})

in

#"Changed Type"

This table has 1 column called 'Material' which can have No Data (null), 1 Material in row 1, or multiple Materials which I want to use to filter ZQM14.LTC in the following manner.

Material Selection Table Possibilities	Return from ZQM14.LTC
Blank (Null)	All rows
1 Material eg ID1020	All rows where Material = ID1020
Multiple Materials eg ID1020, ID1021, ID1022 (all on separate rows)	All rows where Material = ID1020 or Material = ID1022 or Material = ID1022

So I need to be able to filter the Material field in ZQM14.LTC based on the Material Selection (which is a table on the workbook). Noting the ZQM14.LTC table has many columns, too many to list in the fashion shown in your example. Is there a way to accomplish this goal?

Hopefully that makes sense!

Thanks for your help ?

Caleeco

 

[sandy666]

ok,
first of all I suggest to use short labels in steps, eg.
#"Expanded tbl_VendorGroup" = Table.ExpandTableColumn(ZQM14, "tbl_VendorGroup", {"Vendor Group"}, {"tbl_VendorGroup.Vendor Group"}),
to
tbl_VendorGroup = Table.ExpandTableColumn(ZQM14, "tbl_VendorGroup", {"Vendor Group"}, {"tbl_VendorGroup.Vendor Group"}),
because repeat the same is not necessary. I see this is Expand
or just ExpVendorGroup =
some people write poems like: #"This is first step which Expand table from blabla bla bla bla bla" = ...

second: does your table ZQM14.LTC (result of the big M above) contain column Material?

third: create list from table3 for DataValidationList (classic Data Validation) then use as first line in the big Query
DV = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
then merge DV with ZQM14.LTC by [Material] after last step in you big Query as you can see in my M with Mrg step

there will be some differences because I used named range, if you've table, the name of column in DV will be different not Column1, I think

 

[sandy666]

too late... cannot edit post above
my [Column1] will be your [Material Selection Table Possibilities] (again poem ), maybe simply: [Choose Material] ?

 

[sandy666]

to be clear

 

[Caleeco]

Hi Sandy,

Thanks for the additional input, it's really helpful!

I had a play around with it and modified it slightly.
- I needed an Inner Join so that I could Match for multiple materials.
- I switched to a table input instead of a Data Validation list (again so I can pick multiple parts)

Note: I did try using short labels, but was getting a circular reference error. So I just used prefixes of exp. This is my latest M Code (only the last step) which seems to work in testing!

So thank you again for your help, I really appreciate your time

    Merge.LTC = Table.NestedJoin(ZQM14.LTI, {"ZI33 C"}, LongText_Causes, {"Object Reference"}, "LongText_Causes", JoinKind.LeftOuter),
    expLongText_Causes = Table.ExpandTableColumn(Merge.LTC, "LongText_Causes", {"Cause Long Text"}, {"LongText_Causes.Cause Long Text"}),
    ZQM14.LTC = Table.ReplaceValue(expLongText_Causes,null,"Long Text Missing",Replacer.ReplaceValue,{"LongText_Causes.Cause Long Text"}),
    
    Merge.Out = Table.NestedJoin(ZQM14.LTC, {"Material"}, Material_Selection, {"Material"}, "Material_Selection", if Material_Selection{0}[Material]=null then JoinKind.LeftAnti else JoinKind.Inner)
in
    Merge.Out

 

[sandy666]

You are welcome

My M and suggestion about labels are NOT MANDATORY (as I said this is example only) but M looks more clear and less scrolling horizontally
btw. use underscore instead of dot, less troubles in the future with more complicated queries
eg. Merge.Out ==> Merge_Out

I am glad it works for you, thanks for the feedback