Forum Discussion

tross4012's avatar
tross4012
Icon for Helper I rankHelper I
3 years ago
Solved

How to apply applied steps to each record in a table

Hey everyone,

 

I'm running into some issues with the current query I have and was hoping that someone might be able to point me in the right direction. As you can see in the picture 1, I am passing a value ("Column1") into the "URL" column. This URL gets the data from the api and loads the results into the "Record" column. 

1.

Originally, I thought that if I clean the data for the "Table" in any one of these rows that it would apply all other tables in the "Record" column but it appears that the applied steps you'll see below only apply to the first table/record on the list. 

2.

 

How can I iterate through the "Record" column (picture 1) and apply the same applied steps (picture 2) to each record? 

Need Help
Tutorial Requests
  • baghdadi62's avatar
    baghdadi62
    3 years ago

    Hi,

    You need to create a custom column and formulate the expressions as per your specific requirements for the created tables (Record column). The choice of the formula depends on your objective. Please state your needs, and I might be able to assist you.

2 Replies

  • baghdadi62's avatar
    baghdadi62
    Icon for Resolver III rankResolver III
    3 years ago

    Hi,

    You need to create a custom column and formulate the expressions as per your specific requirements for the created tables (Record column). The choice of the formula depends on your objective. Please state your needs, and I might be able to assist you.

  • tross4012's avatar
    tross4012
    Icon for Helper I rankHelper I
    3 years ago

    Hey Baghdadi,

     

    I need to have each of the values in the "Record" column subjected to the following steps:

    1. #"Changed Type1" = Table.TransformColumnTypes(Record,{{" <FullStudy Rank=""1"">", type text}}),
    2. #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", " <FullStudy Rank=""1"">",
    3. Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv), {" <FullStudy Rank=""1"">.1", " <FullStudy Rank=""1"">.2"}),
    4. #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{" <FullStudy Rank=""1"">.1", type text}, {" <FullStudy Rank=""1"">.2", type text}}),
    5. #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", " <FullStudy Rank=""1"">.2", Splitter.SplitTextByDelimiter("<", QuoteStyle.Csv), {" <FullStudy Rank=""1"">.2.1", " <FullStudy Rank=""1"">.2.2"}),
    6. #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{" <FullStudy Rank=""1"">.2.1", type text}, {" <FullStudy Rank=""1"">.2.2", type text}}),
    7. #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{" <FullStudy Rank=""1"">.2.2"}),
    8. #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    9. #"Inserted Trimmed Text" = Table.AddColumn(#"Added Index", "Trim", each Text.Trim([#" <FullStudy Rank=""1"">.1"]), type text),
    10. #"Reordered Columns" = Table.ReorderColumns(#"Inserted Trimmed Text",{" <FullStudy Rank=""1"">.1", "Trim", " <FullStudy Rank=""1"">.2.1", "Index"}),
    11. #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{" <FullStudy Rank=""1"">.1"}),
    12. #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Trim] = "<Field Name=LocationCity" or [Trim] = "<Field Name=LocationCountry" or [Trim] = "<Field Name=LocationFacility" or [Trim] = "<Field Name=LocationState" or [Trim] = "<Field Name=LocationZip" or [Trim] = "<Field Name=NCTId")),
    13. #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Trim] = "<Field Name=LocationFacility" then [Index] else null),
    14. #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    15. #"Removed Columns2" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    16. #"Added Conditional Column1" = Table.AddColumn(#"Removed Columns2", "Headers", each if [Trim] = "<Field Name=NCTId" then "NCTId" else if [Trim] = "<Field Name=LocationFacility" then "Location Facility" else if [Trim] = "<Field Name=LocationCity" then "Location City" else if [Trim] = "<Field Name=LocationState" then "Location State" else if [Trim] = "<Field Name=LocationZip" then "Location Zip" else if [Trim] = "<Field Name=LocationCountry" then "Location Country" else null),
    17. #"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column1",{"Trim", "Headers", " <FullStudy Rank=""1"">.2.1", "Custom"}),
    18. #"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns1",{"Trim"}),
    19. #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{" <FullStudy Rank=""1"">.2.1", "Values"}}),
    20. #"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[Headers]), "Headers", "Values"),
    21. #"Removed Columns4" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})

     

    The steps above clean and format the data associated with the "Table" in the "Record" column however, I need them to apply to each "Table" in each row, not just the first one.