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, 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. 🙂