Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hy guys,
I've 2 tables.
The table1 contains 2 columns: Reference and Description
Ref 1 | 1-TTT-001 - blablabla
Ref 2 | blablabla - 3-XXX-001
Ref 3 | blabla (2-VDF-106) blablabla
The table2 contains 2 columns: Item Code and Item Description.
1-TTT-001 | Item 1
3-XXX-001 | Item 2
2-VDF-106 | Item 3
I would like to be able to know which reference in the table 1 refer to an item of the table 2.
Ref 1 | 1-TTT-001 - blablabla | 1-TTT-001
Ref 2 | blablabla - 3-XXX-001 | 3-XXX-001
Ref 3 | blablabla blabla | null
Ref 4 | blabla (2-VDF-106) blablabla | 2-VDF-106
the point is that the structure of the item code is always X-XXX-XXX. I also think it could be useful but I don't see how to do it in M query.
if Text.Contains([Item Description, something like X-XXX-XXX) then display this thing else null
Do you think it is feasible ?
It would be perfect because I cannot exclude the item codes one y one as I have more than 300 !
Regards,
CR
Solved! Go to Solution.
Hi @CR ,
Try the following code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUzBU0lEy1A0JCdE1MDBU0FVIykmEIKVYHYgKI6AKuKiC7qEFxroREREg5XAlxnAlChpGumEubrqGBmaaWMwyASo8tABhGoQ6tEABCR1aoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Reference " = _t, Description = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference ", type text}, {"Description", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let d = [Description] in Table.SelectRows(#"Table (2)",each Text.Contains(d,[Item Code ]))), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Item Code"}, {"Custom.Item Code"}) in #"Expanded Custom"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thansk a lot @v-xuding-msft it works perfectly.
Personnaly, I never used the let like this. Now, I know !
Regards,
Camille
Hi @CR ,
Try the following code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUzBU0lEy1A0JCdE1MDBU0FVIykmEIKVYHYgKI6AKuKiC7qEFxroREREg5XAlxnAlChpGumEubrqGBmaaWMwyASo8tABhGoQ6tEABCR1aoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Reference " = _t, Description = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference ", type text}, {"Description", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let d = [Description] in Table.SelectRows(#"Table (2)",each Text.Contains(d,[Item Code ]))), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Item Code"}, {"Custom.Item Code"}) in #"Expanded Custom"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thansk a lot @v-xuding-msft it works perfectly.
Personnaly, I never used the let like this. Now, I know !
Regards,
Camille
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |