Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Flag column for first instance

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 xxx...
Need Help
NaveenGandhi's avatar
NaveenGandhi
Memorable Member
3 years ago

Hello Anonymous 

Your question is very unclear, Can you explain it a bit elaborate and also provide the desired output for the above sample data.

 

Regards,

Naveen

Anonymous's avatar
Anonymous
Not applicable
3 years ago

This is what I would like it to do.

 

MachineWork OrderMaterialBatchProduction DayMachine ScrapGood QtyFirst Instance
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341641
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM331037341640
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445891
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM294527445890
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195281
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM315778195280
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228911
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM373374228910
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145931
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM225848145930
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817851
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM270799817850
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Production Day and Machine

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        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.