Forum Discussion
Solved
Filter a table with values from another table (relationship one to many)
Hi! I am trying to solve a problem which at first sight looks super simple, but somehow seems to be quite challenging. I have two tables WorkItem and CustomField with a relationship one to many, se...
Lukas_G you are absolutely right! I did not look to the data close enough.
Please transform you CustomField table in PowerQuery as following:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQosTSwqSS0CsgyVYnUgYiGpiblAyhEsYIRFkRFCkRNYwBhFkQlcDKrIGSxggqLICC6GsC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemFK = _t, CF_Name = _t, CF_Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemFK", Int64.Type}, {"CF_Name", type text}, {"CF_Value", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[CF_Name]), "CF_Name", "CF_Value") in #"Pivoted Column"It will give you table which you can slice by custom field combination.
Hi alena2k , thanks for your reply 🙂
I tried what you suggest, but it unfortunately doesn't do the job.
Lukas_G you are absolutely right! I did not look to the data close enough.
Please transform you CustomField table in PowerQuery as following:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQosTSwqSS0CsgyVYnUgYiGpiblAyhEsYIRFkRFCkRNYwBhFkQlcDKrIGSxggqLICC6GsC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemFK = _t, CF_Name = _t, CF_Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemFK", Int64.Type}, {"CF_Name", type text}, {"CF_Value", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[CF_Name]), "CF_Name", "CF_Value")
in
#"Pivoted Column"
It will give you table which you can slice by custom field combination.