March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Solved! Go to 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.
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.
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.
@mahenkj2 @ddpl @HotChilli @PC2790 Thanks guys! I appreciate your quick response!
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.
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
90 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |