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.
I have to replicate a series of steps to many files. These files are loaded into power query as a table named "main". Column 1 is an index, and columns 2 and 3 have variable names depending on the files.
On the other hand I have a table called CLDR that has three columns: Character, Category and Languages. One of the Category column values is "Main Letters".
In a blank query I want to buffer the following:
I attach the files here https://drive.google.com/drive/folders/1uxFMcvqB6lTS7OCb_YF7_iD6A-jqa1Nx?usp=sharing
Sorry if I'm asking for something too complicated.
Solved! Go to Solution.
thanks to @edhans for including me in the list of possible "solvers".
I understood that the goal is achievable with expressions like this (this is for SecondLetters, but the others are very similar):
let
Source = CLDR,
colsMain=Table.ColumnNames(main),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Languages], colsMain{1})and [Category] = "Main Letters")
in
#"Filtered Rows"
In any case you can download the file PBIX where I put all together
per scaricarli, fai click sul seguente link e segui le istruzioni.
thanks to @edhans for including me in the list of possible "solvers".
I understood that the goal is achievable with expressions like this (this is for SecondLetters, but the others are very similar):
let
Source = CLDR,
colsMain=Table.ColumnNames(main),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Languages], colsMain{1})and [Category] = "Main Letters")
in
#"Filtered Rows"
In any case you can download the file PBIX where I put all together
per scaricarli, fai click sul seguente link e segui le istruzioni.
THANK YOU! that was exactly what I was looking for. I could not open your file because I have not bought power BI, I work with power query from excel 365, but your example was still enough. Simple and functional. All I had to do is add those tables to the buffer. My final code looked like this.
let
Source = CLDR,
colsMain=Table.ColumnNames(main),
Second.Letters = List.Buffer(Table.SelectRows(Source, each Text.Contains([Languages], colsMain{1})and [Category] = "Main Letters")[Character]),
Third.Letters = List.Buffer(Table.SelectRows(Source, each Text.Contains([Languages], colsMain{2})and [Category] = "Main Letters")[Character]),
Second.Simbols = List.Buffer(Table.SelectRows(Source, each Text.Contains([Languages], colsMain{1})and [Category] <> "Main Letters")[Character]),
Third.Simbols = List.Buffer(Table.SelectRows(Source, each Text.Contains([Languages], colsMain{2})and [Category] <> "Main Letters")[Character])
in
Third.Simbols
Again thank you very much
@carlpaul153 I have your files but I have to admit I am having trouble understanding what you want. For example:
"Second.Letters = Rows of the column "Character", in which the name of the second column of "main" is included in the column "Languages", and the Category section is equal to "Main Letters"."
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSecond.Letters should look like this image. Third Letters would be similar but would include characters like ñ, á, é, í, ó, ú ...
Second.Simbols and Third.Simbols would be the symbols (characters other than main letters) of the languages "en" and "es" respectively. Please, any questions ask me. I hope I have explained myself well.
Hi @carlpaul153 - I'm still not grasping your goal. Someone else may want to jump in, but your best course of action may be to contact Miguel at his blog and use his consulting services to get this going. It is going to take quite a bit of time (at least an hour or so, perhaps more) to parse this out and get it working, and it seems to me beyond the general scope of "how to do this" for a peer support forum.
I'll tag a few others that I know are good in Power Query and perhaps they will want to jump in, but beyond that, it might be best to ask the person that wrote that article.
@mahoneypat @Anonymous @ImkeF
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks @edhans.
@carlpaul153 - Just to be clear, that was me passing the buck! 🙂
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.