Search code examples
duplicatesfindrowpowerquery

Finding duplicate row and flagging first row as 1 and remaining duplicate rows 0 in power query

I want solutions for power query.

I have one column of document numbers but it's comes as per materials sold so same document number rows increased, I need to define first row as 1 and remaining rows as 0 in Excel power query.

If anyone have solution then please share to me like m function/formula

Solution

  • Right click the Documentnumber column, choose Group By, use all the default selections

    Adjust the formula in the formula bar from

    = Table.Group(Source, {"Documentnumber"}, {{"Count", each Table.RowCount(_), type number}})

    to

    = Table.Group(Source, {"Documentnumber"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})

    Expand the index column using the arrows atop the new column

    Add column .. custom column ... with formula

    = If Index = 1 then 1 else 0

    Remove index column

    File .. close and load...

    Sample code

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Documentnumber"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Index"}, {"Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Index]=1 then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in #"Removed Columns"