Replacing multiple valuse in Query

[Alirezam]

Hi mates,

I have 2 tables. I want to replace one column's values with the second table. According to the photo attached, I want to replace 1 with a, 2 with b, 3 with c, so on.

I know I can do it with lookupvalue but I do not know how to use this function in Query editor where we can only relace single value each time.

I appreciate that,

[danextian]

Hi @Alirezam ,

Try this in Power Query:

let 
//this will select the value from a list based on position where 1 is position 0
x = {"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k"}
in  x{[Halfhour]-1}

or if the list is from another table

let 
//this will convert the values of the column holding the replacement values to a list. Make sure that the values are in the desired order.
x = AnotherTable[ColumnName] 
in  
//this will select the value from a list based on position where 1 is position 0
x{[Halfhour]-1}

[v-juanli-msft]

Hi @Alirezam 

If the problem is sloved, could you kindly accept it as a solution so to close the case?

If not, please try the method below:

If your data is simple as your screenshot, create a match table, then merge queries in your main table, finally expand the column to get the "Letter" column.

If your data is complex, please show me some examples.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vivran22]

Hello@Alirezam 

What is the structure of table 2?

Col 1: Half Hour (with values from 1 to 26) & Col 2: Letters (with values from a to z)?

If this is so, then you can use Merge Queries option in Power Query

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[amitchandak]

Based on data share I am not sure what columns are common in two table.  But you can always get values from one table to another using x function like

New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project]),table2[name])

Condition may vary, minx, countx, sumx can also be used.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Alirezam]

Thanks Amit, I simply want to replace the left yellow comlumn with the second yellow column. They are in 2 different tables.

[vivran22]

Hello @Alirezam,

Has your problem got resolved?

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/