Forum Discussion
Split a Single Column with delimiter [colon]
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.
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.
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.
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.
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.