Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JeroenR
Helper V
Helper V

Need help to combine two list columns

I have the following two lists extracted with the comma as a delimiter (see picture below):

power bi.png

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.

 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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



View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

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



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

Mariusz
Community Champion
Community Champion

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors