Forum Discussion
ReplaceValue of each Table Record
PQ Gurus,
As you can see, I have a Table on each record.
What I'd like to accomplish (if possible) is to do via Table.ReplaceValue( ... , each ... , Replacer.ReplaceText() ) on each of the table record under the column field [Full Description], without expanding. In other words, I'd like to achieve the replace value first before expanding the tables.
The [Full Description] text has a RegEx of ##### - ####.* - VariousLength.
So the logic that I want to do here is that for each of the table record, on on the "preview" [Full Description] replace the 'old text' by the string that's coming from LEFT([Full Description], Text.PositionOf([Full Description], "-") of the 2nd occurance, with the 'new text' as empty string
After I've able to do the above, only then I'd expand the tables of each record
Kind of tough since you force someone to create an example and don't show desired results. Hopefully I have understood you well enough that you can adapt this code to your real problem.
BTW, I really don't understand your stated logic. An example of before and after would clarify that, but you can probably change the logic in the code below to suit your requirements.
Your file is not useful since the data source is not included.
But try something like the code below, which will transform each table in your column of tables before you expand them:
Before:
let //Replace Source with your table that has a column populated by Tables Source = Table.FromColumns({{Table9,Table10,Table11}}, type table[Table=table]), //Transform each table according to your stated logic //you may need to modify this as I probably mis-understood what you require replace = Table.TransformColumns(Source,{ {"Table", (t)=>Table.ReplaceValue( t, each [Full Description], null, (x,y,z)=>List.Skip(Text.Split(y,"-"),2){0}, {"Full Description"}) } }) in replaceAfter:
7 Replies
As you can see, I have a Table on each record.These are not records, these are list items (values in a column)
Consequently, you need to use List.Transform.
Apologies for not getting correct terminology. Was thinking of each row as record from SQL side.
That being said, you mentioned of using List.Transform, are you able to elaborate further as to how I'd like to achieve my desired result using List.Transform?
Even in SQL a record is a row, not a column. 🙂
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Kind of tough since you force someone to create an example and don't show desired results. Hopefully I have understood you well enough that you can adapt this code to your real problem.
BTW, I really don't understand your stated logic. An example of before and after would clarify that, but you can probably change the logic in the code below to suit your requirements.
Your file is not useful since the data source is not included.
But try something like the code below, which will transform each table in your column of tables before you expand them:
Before:
let //Replace Source with your table that has a column populated by Tables Source = Table.FromColumns({{Table9,Table10,Table11}}, type table[Table=table]), //Transform each table according to your stated logic //you may need to modify this as I probably mis-understood what you require replace = Table.TransformColumns(Source,{ {"Table", (t)=>Table.ReplaceValue( t, each [Full Description], null, (x,y,z)=>List.Skip(Text.Split(y,"-"),2){0}, {"Full Description"}) } }) in replaceAfter:
Result is what I want.
Thank you!
