Looking for a value in a column and adding data another

[automated_user]

Hi, 

I have one table here and no other tables: 

This Table has a column called "PseudoNo" Column and seperates the string by the "," .  The "RowCount.PseudoLines" column listed here:

Is the outcome of the seperated string that when broken up has 1 value "TSF24" and 2 Values of "TS12BLW". 

So for each value in "PartNum" search for it in the  "RowCount.PseudoLines" column, if there's a match, add the "PartDescr" to the "PartDesc" column. 

I was able to do it in Power Query using this function here:

let a=Table.Buffer(Table.Distinct(#"Added Custom7","PartNum")) in Table.TransformColumns(#"Added Custom7",{"ID",each Table.AddColumn( _,"PartDescr",each a {[PartNum=[PseudoLines]]}?[PartDescr]?)})

but the table I have is over 450,000 records and was loading the data very slowly. So is this possible in DAX?

Thanks.

[v-cgao-msft]

Hi @automated_user ,

'If you can do it in M and DAX, then do it in M. I’m a big advocate of pushing transforms and calculations as far upstream as possible. Ideally you leave DAX to do the absolute minimum – especially when data volumes get large.'

M or DAX? That is the Question!

The M function is better at ETL while DAX is better at counting values. As a rule of thumb, if I want an entity column, I also tend to prefer to do it in PQ.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[automated_user]

Hi @v-cgao-msft ,

Thanks for the reply and suggestions! Is there a better way to calculate in M for this function below then: 

let a=Table.Buffer(Table.Distinct(#"Added Custom7","PartNum")) in Table.TransformColumns(#"Added Custom7",{"ID",each Table.AddColumn( _,"PartDescr",each a {[PartNum=[PseudoLines]]}?[PartDescr]?)})

for what I'm trying to do below, The "ID" column is the nested table within the table:

The function I use now is extremely slow when waiting to apply the changes (3 hrs+)