Forum Discussion
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 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.
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.
7 Replies
- Ashish_Mathur
Super User
Hi,
What happense when you click on "Expand to new rows"?
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.
