Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Split a Single Column with delimiter [colon]

Hi all: Good day! I get this column, locations which has a list of location Id & location name delimited by colon [:]. I want to split the column into those two columns, location id & location name b...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Ashish_Mathur, Mariusz, Anonymous : Good day! Really appreciate your patience.

    I tried to change the last part of the code as below:

    ------------------------------------------------

    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}, {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}),

        Split = List.Split(#"Expanded data1", "locations",2),

        output =#table(type table[locationId= Int64.Type, LocationName=text], Split)

        in

        #"Expanded data1"

    --------------------------

    But still it keeps the ‘locations” Column with ‘List’

     If I do as below:

    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}, {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}),

        Split = List.Split("locations",2),

        output =#table(type table[locationId= Int64.Type, LocationName=text], Split)

      

    in

        #"Expanded data1"

    ----------------------

    I get same results : ‘List’ in ‘locations’  Column.

    I am missing some steps, any help is highly appreciated. Many thanks.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Ashish_Mathur, Anonymous and all: Good day! Continuation from my previous post:

Now as I try to change the last part of the code to get the LocationId and locationName from ‘locations’ List using Split Column by Delimiter:

#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}, {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}),

 #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded data1", "locations", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"locations.1", "locations.2"}),

 #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"locations.1", Int64.Type}, {"locations.2", type text}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"locations.1", "locations.LocationId"}, {"locations.2", "locations.LocationName"}})

in

    #"Renamed Columns"

-------------------------------------------

I get errors:

In 'Split Column by Delimiter', I am using "Splitter.SplitTextByDelimiter" - What do I use to get from List? Many thanks in advance.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Ashish_Mathur, Mariusz, Anonymous : Good day! Really appreciate your patience.

I tried to change the last part of the code as below:

------------------------------------------------

#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}, {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}),

    Split = List.Split(#"Expanded data1", "locations",2),

    output =#table(type table[locationId= Int64.Type, LocationName=text], Split)

    in

    #"Expanded data1"

--------------------------

But still it keeps the ‘locations” Column with ‘List’

 If I do as below:

#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}, {"pipelineName", "subject", "startDate", "endDate", "quantityEstimateMMBtu", "impactDescription", "details", "updateDate", "locations"}),

    Split = List.Split("locations",2),

    output =#table(type table[locationId= Int64.Type, LocationName=text], Split)

  

in

    #"Expanded data1"

----------------------

I get same results : ‘List’ in ‘locations’  Column.

I am missing some steps, any help is highly appreciated. Many thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  all: Good day! I found checking the data in the data base table and Api portal that for some pipes there will be same event posted for different locations [as they are impacted for all those locations for a pipeline]. That's why the multiple location rows are coming for those pipes! So, it's actually not an error/issue, but a fact! So, I am closing this thread. Thanks all for your patience. 🙂