Forum Discussion
ReplaceValue of each Table Record
- 1 year ago
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:
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?
- lbendlin1 year ago
Super User
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- JustDavid1 year ago
Helper V
Interesting....in SSMS, it shows me xxx records to show the number of rows.
Although my screenshot is only showing 1 column, but my point here is that I have at least 16 rows (which is what I meant by records), and that in each of that row, is a Table data type.
As for sample file, I've attach in my original post, but I'm unable to show my desired result as if I could, then I won't be asking questions on this forum.
But to make it short, what I'm trying to do here is that BEFORE expanding the tables, in each of that Table data type in each of the row, I'd like to replace the whatever string/text that comes BEFORE the 2nd occurrance of delimter dash (-) with a blank value so that it gives me a "clean" description.
- lbendlin1 year ago
Super User
Your Power Query code refers to a Test.xlsx file. That seems to contain a number of sheets and potentially tables (those are different concepts in Excel). Your code then ends up with a list of these objects. Which transform are you planning to do on each of these sheets/tables?
Here is an example that replaces the table object with its row (record) count.