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.
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
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
Thanks for you answer and take time for my probleme,
But I did not clearly explain my problem in real condition.
The principle stays the same. I want to add the values in the "Réseau" column (see PICTURE_2.png) to a new column in the "Patient" table. To do this I search in the patient table, "Complementaire" column (see PICTURE_1.png), the values corresponding to the column "Nom OCAM" (see PICTURE_2.png). But to do this I have to use the Text.Contains function because the data is not cleaned up.
PICTURE_1.png
PICTURE_2.png
hi, @rfaure
OK!
You must include the "Réseau" column of the table "MatriceOCAM"
However, the correspondence must be through the "Text.Contains" function, ie for each line of the "Complementaire" field of the "Patient" table, it should look in the "Nom OCAM" column of the "Réseau" table.
it is?
If it is not very different from what I sent, but do it here and send it ...
I try again to adapt your solution this week.
Thanks again
@rfaure - are you aware of the fact, that multiple strings can be contained in your data? What is your expected result then?
In your first post for example:
Column1:
AAA-CCC/CCC --->AGRA
CCC BBB/DDD ---> BGRB
DDD-AAA CCC ---> AGRA
But what if u have
AAA-BBB/CCC ---> both "AAA" and "BBB" are matched. What should be displayed in the other column? Only AGRA? Only BGRB? Both?
@eniX Hello,
In these conditions, I take the first value found. Anyway the original problem comes from the database used which is not clean. We are looking to fix this problem on the SQL server. But it takes time because we go through a provider.
@Rfranca Your solution does not work and does not answer the problem. sometimes the result is wrong. I thank you all for your help. I take again your example to explain what do not agree.
I come back to my function from the beginning.
I have a table named "MatriceOCAM" with two columns "Nom OCAM" and "Réseau"
"Nom OCAM" contains the elements to search in a string. "Network" contains the searched elements (when an element of "OCAM Name" has been found, I want to recover the associated "Network" element)
For this function I create in "Get Data" a new "Blank Query"
WordSearch = Table.Column(#"MatriceOCAM", "Nom OCAM"),
Here I try to load in a variable the column "Nom OCAM" of the table "MatriceOCAM"
WordWanted = Table.Column(#"MatriceOCAM", "Réseau"),
Here, this is the same but for the column "Réseau"
SearchWord = (InputText, Position) => if Text.Contains(InputText,WordSearch(Position))= true then Result = WordWanted(Position) else @SearchWord(InputText, Position+1)
Then here, I created a recursive function with an input parameter string and the last known position. In the function I search with the function "Text.Contains" if my string contains one of the elements of the column "Nom OCAM" of the table "MatriceOCAM". A "Position" counter allows me to know the position in the list at each loop turn. If the "Text.Contains" function returns me "true" then I get the element from the "Réseau" column linked to the "Position" counter. Otherwise I call my recursive function and I increment "Position".
The advanced editor sends me the following error on my function:
HI, @eniX
sorry
Some alternatives to solve your problem:
1. Send the two columns of data as a sample to validate my solution.
or
2. I believe that if you just need the records that match your image, try deleting the [COLUMN1] column and filter the [CUSTOM] column with only the "Ok!".
You can find my data on xlsx in a zip with this link :
https://takeafile.com/?f=tuyegijesu
Thanks you
hi, @rfaure
Hi
Check if this is what you need?
One only question that remained is when an [Complémentaire] has two [Réseau] like you want to do with it.
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
I'm sorry, I did not really understand the manipulation. My adaptation was completely erratic. But your solution works properly. thank you very much
hi, @rfaure
To help you apply to your database, I'm sending the sample file.
https://takeafile.com/?f=gisafojiju
Please feel free to ask if you have any other issue.
Best Regards,
Rfranca
I now have a new problem to adapt the solution to the actual database. he returns the following error :
I think I know where this error comes from. Currently my database is created thanks to a multitude of CSV file (monthly extraction). This file is compiled together with a query function that I adapted.
Example :
I have the following directory consists of several csv file that I have to compile into one to form a single table.
Then using a query function I compile all the files.
RecupPatient :
let RecuperationPatient = (MonFichier) => let Source = Csv.Document(File.Contents(MonFichier),[Delimiter=";", Columns=35, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Premières lignes supprimées" = Table.Skip(Source,2), #"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]) in #"En-têtes promus" in RecuperationPatient
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |