Forum Discussion
Need help to combine two list columns
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.
Hi Anonymous
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"Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
3 Replies
Hi Anonymous
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"Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn