Create Index Column on Power Query based on 2 or 3 Columns

[hamzashafiq]

Hi Folks,

I have below dataset, I want to create an index column based on Bill+Ship+Serial and date/invoice_number columns. Each Serial Number has multiple invoices on different dates, so I want to create the index column based on those invoices. The problem I'm facing when two invoices are on same date, my formula put 1,1 for both of those which I don't want. Can anyone help me to do this using Power Query. I don't want to do this using DAX as it affect the performance of report. Thanks

Invoice #	Invoice Date	Bill Customer ID	Ship Customer ID	Serial Number	Bill+Ship+Serial	Index
I1	1-Jan	B1	S1	a1	B1S1a1	1
I2	2-Jan	B1	S1	a1	B1S1a1	2
I3	3-Jan	B1	S1	a1	B1S1a1	3
I4	4-Jan	B1	S1	a1	B1S1a1	4
I5	5-Jan	B1	S1	a1	B1S1a1	5
I6	1-Jan	B2	S2	a2	B2S2a2	1
I7	1-Jan	B2	S2	a2	B2S2a2	2
I8	3-Jan	B2	S2	a2	B2S2a2	3
I9	4-Jan	B2	S2	a2	B2S2a2	4
I10	5-Jan	B2	S2	a2	B2S2a2	5

[HotChilli · Accepted solution]

IN Power Query, first sort your data to make sure it's in a format similar to what you've shown.

Then do a 'Group BY' using the key columns (

Bill Customer ID

Ship Customer ID

Serial Number

Bill+Ship+Serial

) - you might get away with the first 3 if it's enough to show correct groups -

with one aggregation on All rows called 'all'.

Then add a custom column with this:

Table.AddIndexColumn([all], "CountInd",1,1)

This adds an index per Grouping.

You can then expand column headings to return the appropriate data.

A fair bit to get your head around but that should work.

[ddpl · Accepted solution]

Step 1
In Power Query, do...

Group by : Bill+Ship+Serial

New Column Name : Table_1

Operation : All Raws

Step 2

Add Custom Column...

Table_2 = Table.AddIndexColumn([Table_1],"Index",1)

Step 3

Expand Table_2

That's it.

[hamzashafiq]

@mahenkj2 @ddpl @HotChilli @PC2790  Thanks guys! I appreciate your quick response!

[ddpl · Accepted solution]

Step 1
In Power Query, do...

Group by : Bill+Ship+Serial

New Column Name : Table_1

Operation : All Raws

Step 2

Add Custom Column...

Table_2 = Table.AddIndexColumn([Table_1],"Index",1)

Step 3

Expand Table_2

That's it.

[hamzashafiq]

@ddpl That works, thanks alot!

[HotChilli · Accepted solution]

IN Power Query, first sort your data to make sure it's in a format similar to what you've shown.

Then do a 'Group BY' using the key columns (

Bill Customer ID

Ship Customer ID

Serial Number

Bill+Ship+Serial

) - you might get away with the first 3 if it's enough to show correct groups -

with one aggregation on All rows called 'all'.

Then add a custom column with this:

Table.AddIndexColumn([all], "CountInd",1,1)

This adds an index per Grouping.

You can then expand column headings to return the appropriate data.

A fair bit to get your head around but that should work.

[hamzashafiq]

Thanks @HotChilli let me try this!

[mahenkj2]

Hi @hamzashafiq ,

Instead of invoice date, can you use Invoice number in your indexing forumlae. So as 'Bill+Ship+Serial' changes index shall set to 1 and since Invoice number keep incrementing, index has to increment as well.

Assuming you may have no time part in the Invoice date information.

[hamzashafiq]

Yeah we can use it if you think it's useful!

[mahenkj2 · Accepted solution]

Infact, don't need that if data is sorted by Invoice number.

Paste this in advanced editor:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice #", type text}, {"Invoice Date", type datetime}, {"Bill Customer ID", type text}, {"Ship Customer ID", type text}, {"Serial Number", type text}, {"Bill+Ship+Serial", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Bill+Ship+Serial"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "IndexUse", each Table.AddIndexColumn([Count],"IndexNow",1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded IndexUse" = Table.ExpandTableColumn(#"Removed Columns", "IndexUse", {"Invoice #", "Invoice Date", "Bill Customer ID", "Ship Customer ID", "Serial Number", "Bill+Ship+Serial", "Index", "IndexNow"}, {"Invoice #", "Invoice Date", "Bill Customer ID", "Ship Customer ID", "Serial Number", "Bill+Ship+Serial.1", "Index", "IndexNow"})
in
    #"Expanded IndexUse"

Table1 is your sourcedata table name.

Hope it helps.

[PC2790]

Hey @hamzashafiq ,

As per your sample data given, what is the expected output?

[hamzashafiq]

@PC2790  The Index column is the desired output I need.