Forum Discussion
Create a calculated table
Yes, this worked the way I expected. I used a slicer for ITEM and a table for the Names table and it worked like a champ. Here are the queries I used.
Items table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIsSc0F0UZG1kamSrE60UpGUFEQbWhgDRE0hgoagwUNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, ITEMS = _t, TABLE2_IDS = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ITEMS", type text}, {"TABLE2_IDS", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","TABLE2_IDS",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"TABLE2_IDS.1", "TABLE2_IDS.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TABLE2_IDS.1", Int64.Type}, {"TABLE2_IDS.2", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "ITEMS"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "TABLE2_IDS"}}) in #"Renamed Columns"Names table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUfJLzE01VIrViVYyNIByjcBcIyMo1xjChSk2gSqGqTZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, NAMES = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
#"Changed Type"
Thanks both Greg_Deckler and v-micsh-msft for useful suggestions, but perhaps I have to face the problem differently :
I wish I have a filter (filter1) which displays all description items of these table
TABLE1
ID ITEMS TABLE2_IDS
1 Item1 22;25
2 Item2 10;5
3 Item3 100
where TABLE2_IDS contains reference ids to this other table:
TABLE2
ID NAMES
5 Name1
10 Name2
22 Name3
25 Name4
100 Name5
When I select an Item from the above filter I'd like to display into another filter (filter2) all associated Names in table2
e.g. If I select Item2 in filter1 I wish to display Name1 and Name2 in filter2
Yes, this worked the way I expected. I used a slicer for ITEM and a table for the Names table and it worked like a champ. Here are the queries I used.
Items table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIsSc0F0UZG1kamSrE60UpGUFEQbWhgDRE0hgoagwUNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, ITEMS = _t, TABLE2_IDS = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ITEMS", type text}, {"TABLE2_IDS", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","TABLE2_IDS",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"TABLE2_IDS.1", "TABLE2_IDS.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TABLE2_IDS.1", Int64.Type}, {"TABLE2_IDS.2", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "ITEMS"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "TABLE2_IDS"}})
in
#"Renamed Columns"Names table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUfJLzE01VIrViVYyNIByjcBcIyMo1xjChSk2gSqGqTZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, NAMES = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
#"Changed Type"
Thanks, I was wrong setting the cross filter direction in the relationship between the two tables