Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Level up your Power BI skills this month - build one visual each week and tell better stories with data! Get started

Reply
Anonymous
Not applicable

Flag column for first instance

‎06-13-2023 09:05 AM

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

 

MachineWork OrderMaterialBatchProduction DayMachine ScrapGood Qty
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM33103734164
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM33103734164
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM33103734164
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM33103734164
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM33103734164
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM29452744589
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM29452744589
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM29452744589
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM29452744589
xxxxxxxxxxxxxxxx06/01/2023 12:00:00 AM29452744589
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM31577819528
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM31577819528
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM31577819528
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM31577819528
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM31577819528
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM37337422891
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM37337422891
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM37337422891
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM37337422891
xxxxxxxxxxxxxxxx06/02/2023 12:00:00 AM37337422891
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM22584814593
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM22584814593
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM22584814593
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM22584814593
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM22584814593
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM27079981785
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM27079981785
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM27079981785
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM27079981785
xxxxxxxxxxxxxxxx06/03/2023 12:00:00 AM27079981785
Labels:
Message 1 of 7
1,048 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎06-14-2023 07:58 PM

Hi  @Anonymous ,

 

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

vyangliumsft_0-1686797859674.png

2. Create calculated column.

First Instance =
IF(
    [Index]=
MINX(
    FILTER(ALL('Table'),'Table'[Good Qty]=EARLIER('Table'[Good Qty])),[Index]),1,0)

3. Result:

vyangliumsft_1-1686797859681.png

 

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

Flag column for first instance.pbix
Message 7 of 7
986 Views
0
NaveenGandhi
Memorable Member
Memorable Member

‎06-13-2023 10:01 AM

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

Message 2 of 7
1,014 Views
0
Anonymous
Not applicable
In response to NaveenGandhi

‎06-13-2023 11:02 AM

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
Message 3 of 7
1,001 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎06-14-2023 08:12 PM

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 4 of 7
982 Views
0
Anonymous
Not applicable
In response to Ashish_Mathur

‎06-15-2023 05:34 AM

Production Day and Machine

Message 5 of 7
967 Views
0
Ashish_Mathur
Super User
Super User
In response to Anonymous

‎06-15-2023 04:58 PM

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 7
954 Views

Helpful resources

Announcements
April Power BI Update Carousel

Power BI Monthly Update - April 2026

Check out the April 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All