Writing a conditional column from another column in another table

[msupg21]

Table 1 has a column: [region = "3300" as text]

Table 2 has a column [station = "3300 - Southwest Office"]

Essentially, I need to lookup values from table 2's "station" column using table 1's "regions" value of a 4 digit string. Since there should be an exact match in the first four characters of station I should be able to do this in a conditional column add (I think). Then I need to create a new column in table 1 that returns the full value of "station" from table 2 given the match.

Not sure how to do this in the query editor since there are no relationships between these two tables.

[Anonymous · Accepted solution]

HI,@msupg21 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Then station table add custom columns in PowerQuery:

Text.Start([station],4)

3.Finally, use the merge query in the region table to expand:

4.Here are my end results, I hope to meet your needs:

Note that the data types of the columns are consistent when merging queries.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.