The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hello everyone,
I am a beginner in the language M. And I come to call for your help.
I wish in advance to apologize for my writing, I am not good in English. (Thank you Google Translate)
The problem :
I have two tables
first table with the following data:
Coloumn1 |
AAA-CCC/CCC |
CCC BBB/DDD |
DDD-AAA CCC |
second table with the following table :
Column1 | Column2 |
AAA | AGRA |
BBB | BGRB |
In my first table, I try to check each character. If a string corresponds to an element of my column 1 of my second table, I get the corresponding element of my column 2.
I used the following code but I don't understand how to declare some variable :
let WordSearch = Table.Column(#"MatriceOCAM", "Nom OCAM"), WordWanted = Table.Column(#"MatriceOCAM", "Réseau"), SearchWord = (InputText, Position) => if Text.Contains(InputText,WordSearch(Position))= true then Result = WordWanted(Position) else @SearchWord(InputText, Position+1) in Result
Thanks , bye
Solved! Go to Solution.
hi, @rfaure
1. Import to TABLE 1
2. Import to TABLE2
3. Change the query in TABLE1
4. In QUERY EDITOR (the code below)
let Fonte = Excel.Workbook(File.Contents("C:\Users\adm\Desktop\KUNDOO 345233\KUNDOO_345233.xlsx"), null, true), DATES_Sheet = Fonte{[Item="TABELA1",Kind="Sheet"]}[Data], #"Cabeçalhos Promovidos" = Table.PromoteHeaders(DATES_Sheet, [PromoteAllScalars=true]), #"Colunas Renomeadas" = Table.RenameColumns(#"Cabeçalhos Promovidos",{{"Column1", "Column0"}}), #"Personalização Adicionada" = Table.AddColumn(#"Colunas Renomeadas", "TABELAS", each TABELA2 ), #"TABELAS Expandido" = Table.ExpandTableColumn(#"Personalização Adicionada", "TABELAS", {"Column1", "Column2"}, {"Column1", "Column2"}), #"Personalização Adicionada1" = Table.AddColumn(#"TABELAS Expandido", "Personalizar", each if Text.PositionOf([Column0],[Column1])>=0 then "Ok!" else "Nk!"), #"Tipo Alterado" = Table.TransformColumnTypes(#"Personalização Adicionada1",{{"Column0", type text}, {"Column1", type text}, {"Column2", type text}, {"Personalizar", type text}}) in #"Tipo Alterado"
Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |