Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hamzashafiq
Kudo Collector
Kudo Collector

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

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 DateBill Customer IDShip Customer IDSerial NumberBill+Ship+SerialIndex
I11-JanB1S1a1B1S1a11
I22-JanB1S1a1B1S1a12
I33-JanB1S1a1B1S1a13
I44-JanB1S1a1B1S1a14
I55-JanB1S1a1B1S1a15
I61-JanB2S2a2B2S2a21
I71-JanB2S2a2B2S2a22
I83-JanB2S2a2B2S2a23
I94-JanB2S2a2B2S2a24
I105-JanB2S2a2B2S2a25
3 ACCEPTED SOLUTIONS
HotChilli
Super User
Super User

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.

View solution in original post

ddpl
Solution Sage
Solution Sage

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.

View solution in original post

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.

View solution in original post

10 REPLIES 10
hamzashafiq
Kudo Collector
Kudo Collector

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

ddpl
Solution Sage
Solution Sage

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.

@ddpl That works, thanks alot!

HotChilli
Super User
Super User

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.

Thanks @HotChilli let me try this!

mahenkj2
Solution Sage
Solution Sage

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.

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

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
Community Champion
Community Champion

Hey @hamzashafiq ,

 

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.