Split a Single Column with delimiter [colon]

[Anonymous]

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 by the delimiter, colon [:].I am trying to select the column, right click  and find ‘Split by delimiter’. Don’t see that option. Also,  see that Option in the Top Menu greyed out [not enabled]. 

I know it will get activated once I get the data in ''Locations' column as below, but I can't get to that step. 

I tried both options while clicking on the 2 arrows as below: but nothing works!

What am I missing here? Many thanks in advance.

[Ashish_Mathur]

Hi,

What happense when you click on "Expand to new rows"?

[Anonymous]

Hi @Ashish_Mathur and all: Good evening!

I should give d little background:

First I pull data from a sql database as #1 maintenance_events data source, then I create this 2nd data source, #2 details_maintenance_events, which brings ‘locations’ info that has location_id and location_name [: delimited]. There should be 1-to-1 relationship on event ‘Id’: for each maintenance_event [in data source#1], I should get 1 detail info row from data source#2.

I was getting that 1-to-1 info earlier when I got 'locations' column value as location_id : location_name, which I selected 'Split Column by delimiter' and 'locations' would get splitted into 2 columns location_id and location_name. After I had to make some M code changes in my data source code to support “Scheduled Refresh” for publishing the dashboard, I now get 'List' in 'locations' column.

Now, back to your question: If I select “Expand to New Rows” on 'locations', I get ‘Record’ in each cell in that ‘locations’ column

Then if I again expand the ‘Record’, I get to select Id & locationName

But it creates 1 to many relationship from data source#1 to data souce#2, [which shows incorrect data on some visual like ‘Map’.] like for Id=25527, it creates 6 rows.

Where it should always be 1-to-1 as below:

How can I get this 1-to-1 relationship instead of current 1-to-many relation? Many thanks in advance.

[Ashish_Mathur]

Hi,

So then "After I had to make some M code changes in my data source code to support “Scheduled Refresh” for publishing the dashboard, I now get 'List' in 'locations' column."- this is the problem.

[Anonymous]

Hi @Ashish_Mathur : Good day.

The code for this data source, #2 details_maintenance_events is as below [changing some value with xyz/abc/...], from which  I get 'locations' column as a list:

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

let
    Source = Sql.Database("xyz\Fundamentals", "NatGas_V3", [Query="select * from NatGas_V3.natgas.pipeline_maintenance_events#(lf)where endDate >=" &datefilter]),
    #"Sorted Rows" = Table.Sort(Source,{{"endDate", Order.Ascending}, {"updateDate", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each true),
    id1 = #"Filtered Rows"[id],
    #"Converted to Table" = Table.FromList(id1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Source", each Json.Document(Web.Contents("https://api.abc.com/",
    [
        Headers=[#"Gen-Api-Key"="........"],
        RelativePath="natgas/events/v1/maintenance-events/details",
        Query=
        [
            ids=Text.From([Column1]),
            format="json"
        ]
    ]))),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Column1"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Errors", {"Column1"}),
    #"Removed Errors2" = Table.RemoveRowsWithErrors(#"Removed Errors1", {"Source"}),
    #"Expanded Source" = Table.ExpandRecordColumn(#"Removed Errors2", "Source", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandListColumn(#"Expanded Source", "data")
in
    #"Expanded data"

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

First I pull data from a sql database as #1 maintenance_events data source, then I create this 2nd data source, details_maintenance_events. I haven't changed any code in #1 data source[ use get data-> sql database->....], which is as below:

----

let
    Source = Sql.Database("xyz\Fundamentals", "NatGas_V3", [Query="select * from NatGas_V3.natgas.pipeline_maintenance_events#(lf)where endDate >=" &datefilter]),
    #"Sorted Rows" = Table.Sort(Source,{{"endDate", Order.Ascending}, {"updateDate", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each true)
in
    #"Filtered Rows"

--------

How do I change code to get ‘locations’ column as : delimited like location_id:location_name? Many thanks in advance.

[Anonymous]

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 · Accepted solution]

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]

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