Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

compare different values in a table

Hello! I need help finding a solution. I have a table containing a list of employees named "Employee List" with two columns: "month" and "name." I need to determine, for each month: The number of...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello AlienSx, thanks for responding. 

I think I don't need to group. Below example of the data. 

 

can you explain better how I use table.toRecord/ToRows and List. Generate? I am quite new to PowerBi.

Thanks a lot

Valeria

AlienSx's avatar
AlienSx
Super User
2 years ago

Hello, Anonymous you did not show the result you expected to see in the end so I created columns with ee names as lists (joined and left).

let
    Source = your_table,
    g = Table.Group(Source, "Month", {{"e", each _[Name]}}), 
    rows = List.Buffer(Table.ToRecords(g)),
    gen = List.Generate(
        () => 
            [i = 0, 
            r = rows{0} & 
                [joined = {}, 
                n_joined = 0, 
                left = List.Difference(rows{0}[e], try rows{1}[e] otherwise {}), 
                n_left = List.Count(left)]],
        (x) => rows{x[i]}? <> null,
        (x) => 
            [i = x[i] + 1, 
            r = rows{i} & 
                [joined = List.Difference(rows{i}[e], rows{i - 1}[e]),
                n_joined = List.Count(joined),
                left = try List.Difference(rows{i}[e], rows{i + 1}[e]) otherwise {},
                n_left = List.Count(left)]],
        (x) => x[r]
    ),
    z = Table.FromRecords(gen)
in
    z