Load certain rows of a column into the Buffer according to conditions

[carlpaul153]

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:

• 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".
• Second.Simbols = 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 different from "Main Letters".
• Third.Letters = Rows of the column "Character", in which the name of the third column of "main" is included in the column "Languages", and the Category section is equal to "Main Letters"
• Third.Simbols = Rows of the column "Character", in which the name of the third column of "main" is included in the column "Languages", and the Category section is equal to "Main Letters"

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.

[Anonymous · Accepted 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.

https://giga.allegati.tim.it/r?d=alice.it&wr=rocco.lupoi@telecomitalia.it&ws=sprmnt21&e=alice.it&c=F...

[carlpaul153]

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

[edhans]

@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"."

1. Why not normalize the MAIN table and then you can do merges quite easily. It would look like this:

   1. 

2. "in a blank query I want to buffer..." What does that mean? Do you just mean you want to create a new query that is some sort of combination?
3. It would be very helpful if you could mock up the expected results in Excel and post a pic of that. I appreciate the actual data files. Just need help with expected results.

[carlpaul153]

Second.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.

[edhans]

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 

[carlpaul153]

Thanks @edhans.

1. I think I understand what you mean by normalizing the table. With the table you have attached I think my goals could be translated in this way:
   -Second.Letters and Third.Letters = For each value of the "attribute" column (en = 2nd column, es = 3th column), buffer the "Character" column, but only those rows in which the category section is equal to "Main letters".
   -Second.Simbols and Third.Simbols = For each value of the "attribute" column, buffer the "Character" column, but only those rows in which the category section is different from "Main letters".
2. The guide I'm actually basing myself on is this. What I want to do is a modification of step 2, where instead of putting 2 whole columns in Buffer (Old and New in the link), I want to put 4 columns, not complete, but limited with the conditions that I already mentioned.
3. I will immediately put some example outputs. I'm on it.

[Greg_Deckler]

@ImkeF @edhans 

[carlpaul153]

Thanks for showing interest @Greg_Deckler , I'll be waiting 🙂

[Greg_Deckler]

@carlpaul153 - Just to be clear, that was me passing the buck! 🙂