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
AlienSx's avatar
AlienSx
Super User
2 years ago

Hello, Anonymous group data by month to get a table with 2 columns: month and list of employees. Then create a list of rows with (Table.ToRecords or Table.ToRows - upon your liking). Then run List.Generate to calculate what you want using List.Difference(current, previous) to get those who joined and List.Difference(current, next) to get those who left.   

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