Columns values associated with slicer result need to be searched in another 03 tables

[skumar0105]

Hi,
I am struggling with a different type of problem in Power BI report.

All i have is 05 tables named as Material, JupTestResult, SSOResult, CRATable, STBTable.

The base Table is Material Table which contains data as mentioned below

Mat32	Batch32	O.No.	PNT	Mat31	Batch31
320001	12	234	99	310009	21
320001	11	432	99	310009	31
320001	B6	453	99	310009	44
320001	77	675	99	310008	55
320002	A2	785	99	310007	T5
320002	C4	273	99	310007	G4
320003	W2	267	99	310007	09
320003	32	908	99	310007	56
320003	56	902	99	310002	XX

From the above table i will be creating a slicer on Mat32 column and whatever the batches associated with Material starting with 32 having 31 associated batch nos. need to be filtered from other 04 tables  JupTestResult, SSOResult, CRATable, STBTable.

e.g.

for Material code 320001, Batch nos. 12,11,B6,21,31,44 should be searched in other 04 tables.

Material code 320002 A2, C4,T5 and G4 should be searched.

Material Code 320003 W2,32, 09 and 56 should be searched.

rest of the tables will have different information but they will contain columns like Material no. having values starting with 31 and 32 and batch no. column.

How can i achieve this.

So far i have tried creating a search table with batches filtered against Material code selected from Column Mat32 but the output i am getting is blank.

Please help me to solve this.

Regards

Satish

[skumar0105 · Accepted solution]

@jgeddes 
Thanks for the response, however i am using Blank query in New Source to write the Sample code shared by you. but the output i can see is 1 row as Query1 only. i have not created Bridge table anywhere, dont know whether i am using the right way or not.
Also i am using a CSV file in my system to load the data.
My code is 

let
    Source = Csv.Document(File.Contents("d:\Users\satish.kumar1\Downloads\MATERIALDF.csv"), [Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    set_data_types = Table.TransformColumnTypes(Source, {{"Material_Number32", Int64.Type}, {"Batch_Number32", type text}, {"Order_Number", Int64.Type}, {"Plant", Int64.Type}, {"Material_Number31", Int64.Type}, {"Batch_Number31", type text}}),
    get_batch_column_names = List.Select(Table.ColumnNames(set_data_types), each Text.StartsWith(_, "Batch")),
    group_rows = Table.Group(set_data_types, {"Material_Number32"}, {{"all_rows", each Table.SelectColumns(_, get_batch_column_names), type table}}),
    unpivot_nested = Table.TransformColumns(group_rows, {{"all_rows", each Table.Distinct(Table.Unpivot(_, get_batch_column_names, "Column Ref", "Batch No"), "Batch No")}}),
    expand_nested = Table.ExpandTableColumn(unpivot_nested, "all_rows", {"Batch No"}, {"Batch No"})
in
    expand_nested

[skumar0105]

@jgeddes 
Thankyou so much for your response, this was a much needed solution.
The thing i was doing wrong while creating bridge table was, i was writing Query in Blank Query option under New Source in Transform Data.
Correct method was to write the query in Advance editor under Transform data.

Once again thankyou so much for helping out.

Keep Rocking 😎, you made my day 😊

Regards

Satish

[jgeddes]

One way would be to create a bridge table using a reference to the material table and then filtering the tables using the bridge table.
Here is a sample code to create the bridge table. Note that you would replace the source step with the reference to your material table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdAxDsMwCAXQuzBnsMFAMrYdcoFKjRT5/tco3x0a4uULi2cZfJ4kXEqptFDlCJYWuW0RUqOBgiv15QoRTXiCcoNPA1SZYGsZukeYa4JrFKp/iPceCF8zxOX3Db6wBrtMcL88je5nbG0+wRg0wd++Y6oM1TJUGzB/D07HQb1/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mat32 = _t, Batch32 = _t, #"O.No." = _t, PNT = _t, Mat31 = _t, Batch31 = _t]),
    set_data_types = Table.TransformColumnTypes(Source,{{"Mat32", Int64.Type}, {"Batch32", type text}, {"O.No.", Int64.Type}, {"PNT", Int64.Type}, {"Mat31", Int64.Type}, {"Batch31", type text}}),
    get_batch_column_names = List.Select(Table.ColumnNames(set_data_types), each Text.StartsWith(_, "Batch")),
    group_rows = Table.Group(set_data_types, {"Mat32"}, {{"all_rows", each Table.SelectColumns(_, get_batch_column_names), type table}}),
    unpivot_nested = Table.TransformColumns(group_rows, {{"all_rows", each Table.Distinct(Table.Unpivot(_, get_batch_column_names, "Column Ref", "Batch No"), "Batch No")}}),
    expand_nested = Table.ExpandTableColumn(unpivot_nested, "all_rows", {"Batch No"}, {"Batch No"})
in
    expand_nested

Using your example data you would end up with a table like...

Relate the 'Batch No' column to the appropriate columns in the other tables and the 'Batch32' column to the 'Batch32' column in the material table if needed. 
Example below...

You can then use the bridge table to filter.

Hope this helps.

[skumar0105 · Accepted solution]

@jgeddes 
Thanks for the response, however i am using Blank query in New Source to write the Sample code shared by you. but the output i can see is 1 row as Query1 only. i have not created Bridge table anywhere, dont know whether i am using the right way or not.
Also i am using a CSV file in my system to load the data.
My code is 

let
    Source = Csv.Document(File.Contents("d:\Users\satish.kumar1\Downloads\MATERIALDF.csv"), [Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    set_data_types = Table.TransformColumnTypes(Source, {{"Material_Number32", Int64.Type}, {"Batch_Number32", type text}, {"Order_Number", Int64.Type}, {"Plant", Int64.Type}, {"Material_Number31", Int64.Type}, {"Batch_Number31", type text}}),
    get_batch_column_names = List.Select(Table.ColumnNames(set_data_types), each Text.StartsWith(_, "Batch")),
    group_rows = Table.Group(set_data_types, {"Material_Number32"}, {{"all_rows", each Table.SelectColumns(_, get_batch_column_names), type table}}),
    unpivot_nested = Table.TransformColumns(group_rows, {{"all_rows", each Table.Distinct(Table.Unpivot(_, get_batch_column_names, "Column Ref", "Batch No"), "Batch No")}}),
    expand_nested = Table.ExpandTableColumn(unpivot_nested, "all_rows", {"Batch No"}, {"Batch No"})
in
    expand_nested

[jgeddes · Accepted solution]

Here is a pbix file with the model I created. You can see how I created the bridge table and the resulting relationships from it.