Need help to combine two list columns

[JeroenR]

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.

[Mariusz · Accepted 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"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[JeroenR]

Thank you very much! It works great, I couldn't have done it without you.

[Mariusz]

Hi @JeroenR 

Happy to help 🙂

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn