Forum Discussion
Multiple Transformations on a Column of Tables - Power Query
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?
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"})})
10 Replies
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"})})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?
Custom1 is the previous step name,change it to the correct name in your query.
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.
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
JimmyTried 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?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
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 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=TypeThen 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=TypeCan you share a working example?
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!
