Forum Discussion

APQueiroz's avatar
APQueiroz
Frequent Visitor
1 year ago
Solved

Joing tables with conditions using Power Query is not working as it should.

    Hello, experts! I hope you can help me with this issue. Please excuse any language mistakes.   I am working with two tables in Power Query. The first table contains assignment activity data f...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago

    give this a try 

    let
    assignment = Table.FromList(
        List.Combine(
            Table.ToList(
                assignment_data,
                (x) => {{x{0}, x{1}, -1, x{3}}} & {{x{0}, x{2}, 100, "none"}} // speeds -1 and 100 are for sorting
            )
        ),
        (x) => x, 
        Table.ColumnNames(telemetric_data) & {"assignment_id"}
    ), 
    combine_sort = Table.Sort(telemetric_data & assignment, {"equipment_id(Int64)", "timestamp(Datetime)", "speed"}), 
    fd = Table.FillDown(combine_sort, {"assignment_id"}),
    filter = Table.SelectRows(fd, (x) => x[speed] <> -1 and x[speed] <> 100)
in
    filter

    Idea is to combine tables, sort them and fill down assignment_id.

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
1 year ago

give this a try 

let
    assignment = Table.FromList(
        List.Combine(
            Table.ToList(
                assignment_data,
                (x) => {{x{0}, x{1}, -1, x{3}}} & {{x{0}, x{2}, 100, "none"}} // speeds -1 and 100 are for sorting
            )
        ),
        (x) => x, 
        Table.ColumnNames(telemetric_data) & {"assignment_id"}
    ), 
    combine_sort = Table.Sort(telemetric_data & assignment, {"equipment_id(Int64)", "timestamp(Datetime)", "speed"}), 
    fd = Table.FillDown(combine_sort, {"assignment_id"}),
    filter = Table.SelectRows(fd, (x) => x[speed] <> -1 and x[speed] <> 100)
in
    filter

Idea is to combine tables, sort them and fill down assignment_id.

APQueiroz's avatar
APQueiroz
Frequent Visitor
1 year ago

Thank you greatly, AlienSx . That solved my problem—ingenious! It never crossed my mind to use lists like that in Power Query; I thought it would be slower than using tables. It’s perfect for custom unpivot and merging large timeline tables! The only issue is that the column used for sorting cannot contain null values. In my case, I replaced the nulls with a value between the minimum and maximum values.