Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This is my first crack at requesting assistance, so I will do my best:
I have two tables one with a couple million rows, the column in question has roughly 500 unique values (Table 1, Column A ). I have another table with a column of 50 unique values that are portions of some of the values found in 'Column A' (Table 2, Column B). My thought is that I would create a column in Table 1 that compares values in Column A to those that start with values in Table 2 Column B and if a match is found, returns value from Table 2 Column B. I have converted both columns to text, but can't seem to find the proper syntax to complete this task.
I am very green when it comes to DAX and Power Query so I may be going about it all wrong. Any assistance or recommendations would be greatly appreciated. I can try and provide better / clearer information if the below doesn't suffice.
Column A | Column B |
190478 | 117 |
205049 | 206 |
205049 | 1903 |
190378 | 1904 |
190381 | 190381 |
117346 | 2173 |
205349 | 2174 |
213289 | 2130 |
222089 | 2132 |
Solved! Go to Solution.
A loop might be overkill. If the columns involved are text data type, then you can filter Column B using Text.StartsWith within the filter condition like this:
Using your original columns:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0MDG3UIrViVYyMjA1MLFEZwIVGEMVgJgWhhCmobmxiRlMrTFMm6GxkQWUaWRkAGLGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New Column", (r) =>
List.First(
List.Select(
#"Table 2"[Column B],
each Text.StartsWith(r[Column A], _)
), ""
), type text)
in
#"Added Custom"
Apologies,
trying to find the best way to provide the requested information. My responses to not appear to be saved when I provided them. Hopefully this one works and the below information is more helpful.
My thought is the function should be something to the effect of:
New Column = IF Table 1, Column A Text.StartsWith Table 2, Column B then return Table 2,Column B
I know this is not anywhere near correct, but hopefully gives you an Idea of what I am looking to accomplish.
Example of data and expected result.
@CRBailey you can run a loop to do the job where it terminates at the first match; pbix is attached
let
value = Text.From([ColumnA]),
Loop =
List.Generate(
()=>[i=0,j=Text.StartsWith(value,Search{i})],
each [j]<>true,
each[i=[i]+1,j=Text.StartsWith(value,Search{i})],
each [i]
),
index1= if List.Count(Loop)-1 =-1 then 0 else List.Count(Loop)-1,
index2 = if index1=0 then 0 else index1+1
in
try Search{index2} otherwise ""
A loop might be overkill. If the columns involved are text data type, then you can filter Column B using Text.StartsWith within the filter condition like this:
Using your original columns:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0MDG3UIrViVYyMjA1MLFEZwIVGEMVgJgWhhCmobmxiRlMrTFMm6GxkQWUaWRkAGLGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New Column", (r) =>
List.First(
List.Select(
#"Table 2"[Column B],
each Text.StartsWith(r[Column A], _)
), ""
), type text)
in
#"Added Custom"
Thanks @AlexisOlson
Can you please explain this bit where you used r
(r) =>
List.First(
List.Select(
t2[ColumnB],
each Text.StartsWith(r[ColumnA], _)
),"")
I have seen this usage in PQ but don't completely undertsand what it is doing and how it is influencing the calculation.
Because I'm working with two different environments at the same time (the current row and the current item in a list), I use a named function to distinguish between them. Without the "r" inside Text.StartsWith, it thinks I'm trying to refer to [Column A] of the current item.
The "each ... _" construction is equivalent to "(_) =>" so I could have written it like this instead:
(row) =>
List.First(
List.Select(
t2[ColumnB],
(item) => Text.StartsWith(row[ColumnA], item)
),"")
Recommended reading:
https://bengribaudo.com/blog/2017/12/08/4270/power-query-m-primer-part3-functions-function-values-pa...
@AlexisOlson I do think this is closer, you are running into a similar issue I am though. How to get example (like row 5 ) to check that it can't match something more. row 5 should match 190381, not just 1903. would I enter something like a ' List.Last ' function
Yep. Change to List.Last instead of List.First.
A more sophisticated method would be to pick the longest match using List.Max with a Text.Length comparison rule.
(r) =>
List.Max(
List.Select(
#"Table 2"[Column B],
each Text.StartsWith(r[Column A], _)
), "", each Text.Length(_)
)
I'd recommend showing a sample of both tables and what your desired result is. It's not clear what to do with the Column A and Column B you've provided.