Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 l...
Need Help
Show and Tell
Tips and Tricks
  • shaowu459's avatar
    shaowu459
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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?