Multiple Transformations on a Column of Tables - Power Query

[Anonymous]

if i have a Column containing Tables like this:

and before expanding the Tables, i want to do multiple  Text Transformations (and Table transformations) on the "Column1" of each Table, which looks like this:

Column1	Column2	Column3	Column4	Column5
Mean	0.4	0.98	0.34	0.56
Base	200	200	600	400
SomeSegment 1	0.32	0.44	0.54	0.88
SomeSegment 2	0.21	0.76	0.34	0.66
SomeSegment 3	0.12	0.93	0.53	0.74
SomeSegment 4	0.86	0.38	0.29	0.61

for e.g. I want to replace Column1 text using Text functions and an IF...THEN...ELSE condition to look like this:

Column1	Column2	Column3	Column4	Column5
Mean	0.4	0.98	0.34	0.56
Base	200	200	600	400
R0.1	0.32	0.44	0.54	0.88
R0.2	0.21	0.76	0.34	0.66
R0.3	0.12	0.93	0.53	0.74
R0.4	0.86	0.38	0.29	0.61

My question is:

- How do i access each row value of Column1?

- Which all types of Table & Text Transformations can be performed on the column of tables?

[Anonymous]

That's pretty tough.  The best way to do it is to add a new table column that contains the newly transformed column:

= Table.AddColumn(#"PriorStep", "NewTable", each Table.ReplaceValue([Custom],"SomeSegment ","Ro.",Replacer.ReplaceText,{"Column1"}))

Now your column is transformed, and your tables still nested!

[Anonymous]

I would add a column with the transformed tables:

Table.AddColumn(PriorStep, "NewTableColumn", each if Text.StartsWith([Custom][Column1], "ABC") then Text.Select([Custom][Column1], "Efg") else Text.Start([Custom][Column1], 3))

You refer to the inner table columns as each Function.Name([TableColumnName][InnerTableColumn], parameters).

Hope that helps!

[Anonymous]

@Anonymous tried :

Table.AddColumn(PriorStep, "NewTableColumn", each if Text.StartsWith([Custom][Column1], "ABC") then Text.Select([Custom][Column1], "Efg") else Text.Start([Custom][Column1], 3))

it did not work!

Got error:

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=List
    Type=Type

Then tried:

= Table.AddColumn(RemovedColumns, "Custom1", each Table.ReplaceValue([Custom], _ , if Text.Contains(_, "segment", Comparer.OrdinalIgnoreCase) then "R0." & Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}) else _ , Replacer.ReplaceText,{"Column1"}) )

but got error:

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
    Value=Record
    Type=Type

Can you share a working example?

[Jimmy801]

Hello @Anonymous 

you have to use a nested Table.TransformColumns... here some code that you have a idea how it looks like

Table.TransformColumns
(
   PreviousStep,
   {
        {
             "Custom",
             (tbl)=> Table.TransformColumns(tbl, {{"Column1", each if _ = "seg." then "R.01" else it... etcetc....
        }
    }

for the second Table.TransformColums you could also use a Table.ReplaceValue

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 

Tried this:

= Table.TransformColumns( [Custom] {{"Column1", (tbl) => Table.TransformColumns(tbl, {{"Column1", each if Text.Contains(_ , "segment", Comparer.OrdinalIgnoreCase) then "R.01" & _ else _}})}} )

got this error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

[Jimmy801]

Hello @Anonymous 

there are to errors in. The table in your first TransformColumns has to be a table.. in your case the name of your previous step, and the second error is that the "," is missing after the reference. Change this two and everything should be fine

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[shaowu459]

Table.TransformColumns will allow you to access each table in the Custom Column, and Table.ReplaceValue will allow you to replace values in Column1 of each table.

[shaowu459 · Accepted solution]

Hi, @Anonymous , please check if this is what you need.

you have a table, each row of the column "Custom" is a able, like below picture:

You need to do transformation of each row like:

= Table.TransformColumns(Custom1,{"Custom",each Table.ReplaceValue(_,0,0,(x,y,z)=>if Text.Contains(x," ") then "R0."&Text.Split(x," "){1} else x,{"Column1"})})

[Anonymous]

@shaowu459 Thanks for your reply.

I am getting following errors:

Expression.Error: The name 'Custom1' wasn't recognized.  Make sure it's spelled correctly.

Once i replace `Custom1` with `Custom`, i get another error:

2) Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

How were you able to apply the transformation in place on the `Column1` of each table instead of Adding a new column? Can you share your output file?

[shaowu459]

Custom1 is the previous step name,change it to the correct name in your query.