Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the following two lists extracted with the comma as a delimiter (see picture below):
To explain a few little things to understand it a bit better.
The "car_speed_histogram" and "car_speed_bucket" connect with each other as follow: the "car_speed_histogram" and "car_speed_bucket" arrays together give the speed distribution in 10 km / h groups between 0 and 70+ km / h in the hour in question. "Car_speed_histogram" contains the counts for the corresponding histogram bins, which are listed in "car_speed_bucket". In the example all categories are present (category 0 corresponds to 0-10 km / h, category 1 is 10-20 km / h, etc., and category 7 is 70+ km / h), but in a street with little traffic, it is possible that only one car is detected that passed at 55 km / h, then the car_speed_histogram would only contain a 1, and the car_speed_bucke would only contain a 5, which, taken together, indicates that 1 object was detected in the 50-60 km / h category.
How do I combine these two columns in the right way, so that i can analyze them.
Solved! Go to Solution.
Hi @JeroenR
Try the below script + attached file for reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY3BDcAgDAN3yfseGAKlsyAebfffoaF9I0WRY/uUMewyLAuJkql57f+UIkmI8HAqjcMmw+7wFa1GSbjjaY2itAGeBXRqvOjo/EQlb4D5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [segment_id = _t, car_speed_histogram = _t, car_speed_bucke = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each
Record.ToTable(
Record.FromList(
Text.Split( [car_speed_histogram], "," ),
Text.Split( [car_speed_bucke], "," )
)
)
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"segment_id", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Value"}, {"Custom.Name", "Custom.Value"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Name", Int64.Type}, {"Custom.Value", Int64.Type}})
in
#"Changed Type"
Hi @JeroenR
Try the below script + attached file for reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY3BDcAgDAN3yfseGAKlsyAebfffoaF9I0WRY/uUMewyLAuJkql57f+UIkmI8HAqjcMmw+7wFa1GSbjjaY2itAGeBXRqvOjo/EQlb4D5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [segment_id = _t, car_speed_histogram = _t, car_speed_bucke = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each
Record.ToTable(
Record.FromList(
Text.Split( [car_speed_histogram], "," ),
Text.Split( [car_speed_bucke], "," )
)
)
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"segment_id", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Value"}, {"Custom.Name", "Custom.Value"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Name", Int64.Type}, {"Custom.Value", Int64.Type}})
in
#"Changed Type"
Thank you very much! It works great, I couldn't have done it without you.