Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
6 months ago
Solved

Power Query Distinct non repeating Index


Hi, is there a way in power query to create a non repeating, distince index,   so the first of each value is counted, 
but just blank or null for rest,  so no use grouping adding index to group and expanding;     in excel I would use 
something like this is cell I6 ;    =IF( COUNTIF($E$5:E5,E6)>0,"",MAX($I$5:I5)+1), 

my attemps so far which give a number but the original index number not what i wanted, but it's as far as I've got; 

let
  Source = #table(
    type table [Item = text, dex = Int64.Type],
    {
      {"A", 1},
      {"B", 2},
      {"C", 3},
      {"C", 4},
      {"C", 5},
      {"D", 6},
      {"D", 7},
      {"E", 8},
      {"A", 9},
      {"B", 10}
    }
  ),
  Custom1 = Table.AddColumn(
    Source,
    "N",
    (x) =>
      if List.Contains(Table.SelectRows(Source, (y) => y[dex] < x[dex])[Item], x[Item]) = false then
        x[dex]
      else
        ""
  )
in
  Custom1


Richard. 

Need Help
Show and Tell
  • ronrsnfld's avatar
    ronrsnfld
    6 months ago

    List.Generate should work quite rapidly:

    let
  Source = #table(
    type table [Item = text, dex = Int64.Type],
    {
      {"A", 1},
      {"B", 2},
      {"C", 3},
      {"C", 4},
      {"C", 5},
      {"D", 6},
      {"D", 7},
      {"E", 8},
      {"A", 9},
      {"B", 10}
    }),

//Sort by Item
    #"Sorted Rows" = Table.Sort(Source,{{"Item", Order.Ascending}}),

//Add the N column using List.Generate
  #"Add N" = 
      Table.FromColumns(
        Table.ToColumns(#"Sorted Rows")
        & {[i=#"Sorted Rows"[Item],
              b=List.Generate(
                  ()=>[a=1, idx=0],
                  each [idx]<List.Count(i),
                  each [idx=[idx]+1, 
                        a=if i{idx} = i{idx-1} 
                          then null 
                          else List.Count(List.Distinct(List.Range(i,0,idx+1)))],
                  each [a]
              )][b]},
        type table[Item=text, dex=Int64.Type, N=Int64.Type]),

//Sort back to original order using the dex column
    #"Sorted Rows1" = Table.Sort(#"Add N",{{"dex", Order.Ascending}})
in
    #"Sorted Rows1"

     

  • MarkLaf's avatar
    MarkLaf
    6 months ago

    This groups and adds an index, but then combines as columns which results in getting N just once per group. No idea how this performs vs other solutions provided - test on your data and let us know!

     

    let
  Source = #table(
    type table [Item = text, dex = Int64.Type],
    {
      {"A", 1},{"B", 2},{"C", 3},{"C", 4},{"C", 5},
      {"D", 6},{"D", 7},{"E", 8},{"A", 9},{"B", 10}
    }
  ),
    Group = Table.Group(Source, {"Item"}, {{"rows", Table.ToColumns, type {list} }}),
    Index = Table.AddIndexColumn(Group, "N", 1, 1, Int64.Type),
    IndexToCol = Table.TransformColumns(Index,{{"N", each {{_}} , type {{Int64.Type}}}}),
    CombineCols = Table.CombineColumns( 
      IndexToCol, {"rows","N"}, 
      each Table.FromColumns( 
        List.Combine( _ ), 
        type table Type.ForRecord( 
          Type.RecordFields( Type.TableRow( Value.Type( Source ) ) ) 
          & [N=[Type=Int64.Type,Optional=false]], false 
        ) 
      ), 
      "new_tables" 
    ),
    CombineTables = Table.Combine( CombineCols[new_tables] )
in
    CombineTables

     

    Output

     

     

  • AlienSx's avatar
    AlienSx
    6 months ago

    just List.Generate

    let
  Source = #table(
    {"Item", "dex"},
    {{"A", 1},{"B", 2},{"C", 3},{"C", 4},{"C", 5}, {"D", 6},{"D", 7},{"E", 8},{"A", 9},{"B", 10}}
  ),
  all_items = List.Buffer(Source[Item]),
  distinct_items = List.Distinct(all_items),
  gnr = List.Generate(
    () => [i = 0, contains = true, s = List.RemoveItems(distinct_items, {all_items{0}}), c = 1],
    (x) => x[i] < List.Count(all_items),
    (x) => [
      i = x[i] + 1, 
      contains = List.Contains(x[s], all_items{i}),
      s = if contains then List.RemoveItems(x[s], {all_items{i}}) else x[s],
      c = if contains then x[c] + 1 else x[c]
    ],
    (x) => if x[contains] then x[c] else null
  ),
  n = Table.FromColumns(Table.ToColumns(Source) & {gnr}, Table.ColumnNames(Source) & {"N"})
in
  n

7 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    6 months ago

    List.Generate should work quite rapidly:

    let
  Source = #table(
    type table [Item = text, dex = Int64.Type],
    {
      {"A", 1},
      {"B", 2},
      {"C", 3},
      {"C", 4},
      {"C", 5},
      {"D", 6},
      {"D", 7},
      {"E", 8},
      {"A", 9},
      {"B", 10}
    }),

//Sort by Item
    #"Sorted Rows" = Table.Sort(Source,{{"Item", Order.Ascending}}),

//Add the N column using List.Generate
  #"Add N" = 
      Table.FromColumns(
        Table.ToColumns(#"Sorted Rows")
        & {[i=#"Sorted Rows"[Item],
              b=List.Generate(
                  ()=>[a=1, idx=0],
                  each [idx]<List.Count(i),
                  each [idx=[idx]+1, 
                        a=if i{idx} = i{idx-1} 
                          then null 
                          else List.Count(List.Distinct(List.Range(i,0,idx+1)))],
                  each [a]
              )][b]},
        type table[Item=text, dex=Int64.Type, N=Int64.Type]),

//Sort back to original order using the dex column
    #"Sorted Rows1" = Table.Sort(#"Add N",{{"dex", Order.Ascending}})
in
    #"Sorted Rows1"

     

  • MarkLaf's avatar
    MarkLaf
    Super User
    6 months ago

    This groups and adds an index, but then combines as columns which results in getting N just once per group. No idea how this performs vs other solutions provided - test on your data and let us know!

     

    let
  Source = #table(
    type table [Item = text, dex = Int64.Type],
    {
      {"A", 1},{"B", 2},{"C", 3},{"C", 4},{"C", 5},
      {"D", 6},{"D", 7},{"E", 8},{"A", 9},{"B", 10}
    }
  ),
    Group = Table.Group(Source, {"Item"}, {{"rows", Table.ToColumns, type {list} }}),
    Index = Table.AddIndexColumn(Group, "N", 1, 1, Int64.Type),
    IndexToCol = Table.TransformColumns(Index,{{"N", each {{_}} , type {{Int64.Type}}}}),
    CombineCols = Table.CombineColumns( 
      IndexToCol, {"rows","N"}, 
      each Table.FromColumns( 
        List.Combine( _ ), 
        type table Type.ForRecord( 
          Type.RecordFields( Type.TableRow( Value.Type( Source ) ) ) 
          & [N=[Type=Int64.Type,Optional=false]], false 
        ) 
      ), 
      "new_tables" 
    ),
    CombineTables = Table.Combine( CombineCols[new_tables] )
in
    CombineTables

     

    Output

     

     

    • Dicken's avatar
      Dicken
      Post Prodigy
      6 months ago

      Thanks to all who replied;  just to add this was my final solution, which works, but does not keep original order
      but for what its worth ; 

      let
  Source = #table(
    type table [Item = text],
    {
      {"A"},
      {"A"},
      {"B"},
      {"B"},
      {"C"},
      {"C"},
      {"A"},
      {"C"},
      {"A"},
      {"D"},
      {"B"},
      {"D"},
      {"A"},
      {"D"},
      {"D"}
    }
  ),
  group = Table.AddIndexColumn(
    Table.Group(Source, {"Item"}, {{"N", each Table.RowCount(_) - 1}}),
    "dex", 1,1),

  Add_Col = Table.AddColumn(group, "Distinct_Index", each {[dex]} & List.Repeat({""}, [N] - 1)),

  Expand = Table.ExpandListColumn(Add_Col, "Distinct_Index")[[Item], [Distinct_Index]]
in
  Expand
  • AlienSx's avatar
    AlienSx
    Super User
    6 months ago

    just List.Generate

    let
  Source = #table(
    {"Item", "dex"},
    {{"A", 1},{"B", 2},{"C", 3},{"C", 4},{"C", 5}, {"D", 6},{"D", 7},{"E", 8},{"A", 9},{"B", 10}}
  ),
  all_items = List.Buffer(Source[Item]),
  distinct_items = List.Distinct(all_items),
  gnr = List.Generate(
    () => [i = 0, contains = true, s = List.RemoveItems(distinct_items, {all_items{0}}), c = 1],
    (x) => x[i] < List.Count(all_items),
    (x) => [
      i = x[i] + 1, 
      contains = List.Contains(x[s], all_items{i}),
      s = if contains then List.RemoveItems(x[s], {all_items{i}}) else x[s],
      c = if contains then x[c] + 1 else x[c]
    ],
    (x) => if x[contains] then x[c] else null
  ),
  n = Table.FromColumns(Table.ToColumns(Source) & {gnr}, Table.ColumnNames(Source) & {"N"})
in
  n
  • QuentinGa's avatar
    QuentinGa
    Resolver I
    6 months ago

    Hi,


    You can skip the slow grouping or row by row formulas. The fastest way is a “Self-Merge” using the Remove Duplicates feature.

    Here is how to do it:
    - Sort your table by your index column (dex) so the order is fixed.
    - Create a Reference of your query (Right click query > Reference).
    - In this new query, remove duplicates on the [Item] column (this keeps just the first row of each value).
    - Add an Index Column (from 1) to this new list.
    - Go back to your original query and do a Merge.
    - Join with the reference query, but match on TWO columns: select [Item] and [dex] in both tables.
    - Expand the new index column.

     

    Because you match on both the Item name AND the specific row ID (dex), only the first occurrence will find a match. The rest will naturally be null.

     

    Hope it will helps!

    • Dicken's avatar
      Dicken
      Post Prodigy
      6 months ago

      Thanks , can't try this just now, will get back to you,  i have come up wth this, 
      this this adds a column with an Item value when the value is the first,   I need to apply general rule to an Index, 
      but not just now,  so this works on a single column table   Column Name  'Item' ; 

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Item"},
{{ "LIst", each  
 { [Item] {0} } & 
List.Repeat( {""}, 
Table.RowCount(_) -1 ) }}  ),
    #"Expanded LIst" = Table.ExpandListColumn(#"Grouped Rows", "LIst")
in
    #"Expanded LIst"