Flag column for first instance

Question

I need to create a column that returns a 0 or a 1 based on the first instance. Could someone help me?

Machine	Work Order	Material	Batch	Production Day	Machine Scrap	Good Qty
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	33103	734164
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	33103	734164
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	33103	734164
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	33103	734164
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	33103	734164
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	29452	744589
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	29452	744589
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	29452	744589
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	29452	744589
xxxx	xxxx	xxxx	xxxx	06/01/2023 12:00:00 AM	29452	744589
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	31577	819528
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	31577	819528
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	31577	819528
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	31577	819528
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	31577	819528
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	37337	422891
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	37337	422891
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	37337	422891
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	37337	422891
xxxx	xxxx	xxxx	xxxx	06/02/2023 12:00:00 AM	37337	422891
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	22584	814593
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	22584	814593
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	22584	814593
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	22584	814593
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	22584	814593
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	27079	981785
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	27079	981785
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	27079	981785
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	27079	981785
xxxx	xxxx	xxxx	xxxx	06/03/2023 12:00:00 AM	27079	981785

ashish_mathur · Answer

Hi,
This M code works
let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine", type text}, {"Work Order", type text}, {"Material", type text}, {"Batch", type text}, {"Production Day", type datetime}, {"Machine Scrap", Int64.Type}, {"Good Qty", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Machine Scrap", Order.Ascending}, {"Production Day", Order.Ascending}}),
    Partition = Table.Group(#"Sorted Rows", {"Good Qty", "Production Day"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Machine", "Work Order", "Material", "Batch", "Machine Scrap", "Index"}, {"Machine", "Work Order", "Material", "Batch", "Machine Scrap", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Custom", each if [Index] = 1 then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"
Hope this helps.

naveengandhi · Answer

Hello&nbsp;Anonymous&nbsp;Your question is very unclear, Can you explain it a bit elaborate and also provide the desired output for the above sample data.&nbsp;Regards,Naveen

anonymous · Answer

This is what I would like it to do.&nbsp;MachineWork OrderMaterialBatchProduction DayMachine ScrapGood QtyFirst Instancexxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341641xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445891xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195281xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228911xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145931xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817851xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850

anonymous · Answer

Hi&nbsp; Anonymous&nbsp;,
&nbsp;
Here are the steps you can follow：
1. In Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.
First Instance =
IF(
    [Index]=
MINX(
    FILTER(ALL('Table'),'Table'[Good Qty]=EARLIER('Table'[Good Qty])),[Index]),1,0)
3. Result:

&nbsp;
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

ashish_mathur · Answer

Hi,
Even before we inser the First instance column, on what criteria should the data be sorted?

Forum Discussion

Flag column for first instance

6 Replies

Related content

periods of consecutive instances

SSRS and Power on one Instance

Counting instances

New Column

Identify first instance across multiple columns