Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello PowerBi world!
I really could get some advice on my query. I have a customer table which capture customerID and invoiceid:
In the table, i have a customer who can have many invoice ID.
See below
CustomerID | InvoiceID |
12344 | 65425 |
12344 | 65426 |
12343 | 65298 |
12344 | 35029 |
I need help so that I can see the next InvoiceID that is linked to that customer as a coloumn:
Example:
CustomerID | InvoiceID | InvoiceID2 |
12344 | 65425 | 65426 |
12343 | 65298 | |
12344 | 35029 |
Please could you help me in creating a dax or something??
Thank You
Viral
Solved! Go to Solution.
@Anonymous
Paste the following code in Blank Query > Advanced Editor and check the steps:
You can download the file: HERE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFR0lEyMzUxMlWK1UEVMYOLGINFjCwtUNQYmxoYWcJETE3BagwMDNBFDIEisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, InvoiceID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerID"}, {{"All", each _, type table [CustomerID=nullable text, InvoiceID=nullable number]}, {"min", each List.Min([InvoiceID]), type nullable number}, {"max", each List.Max([InvoiceID]), type nullable number}}, GroupKind.Local),
#"Inserted Subtraction" = Table.AddColumn(#"Grouped Rows", "INVOICE ID1", each {[min]..[max]}),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"All", "min", "max"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"INVOICE ID1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "INVOICE ID1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"INVOICE ID1.1", "INVOICE ID1.2", "INVOICE ID1.3", "INVOICE ID1.4", "INVOICE ID1.5", "INVOICE ID1.6", "INVOICE ID1.7", "INVOICE ID1.8", "INVOICE ID1.9", "INVOICE ID1.10", "INVOICE ID1.11"})
in
#"Split Column by Delimiter"
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Paste the following code in Blank Query > Advanced Editor and check the steps:
You can download the file: HERE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFR0lEyMzUxMlWK1UEVMYOLGINFjCwtUNQYmxoYWcJETE3BagwMDNBFDIEisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, InvoiceID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceID", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerID"}, {{"All", each _, type table [CustomerID=nullable text, InvoiceID=nullable number]}, {"min", each List.Min([InvoiceID]), type nullable number}, {"max", each List.Max([InvoiceID]), type nullable number}}, GroupKind.Local),
#"Inserted Subtraction" = Table.AddColumn(#"Grouped Rows", "INVOICE ID1", each {[min]..[max]}),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"All", "min", "max"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"INVOICE ID1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "INVOICE ID1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"INVOICE ID1.1", "INVOICE ID1.2", "INVOICE ID1.3", "INVOICE ID1.4", "INVOICE ID1.5", "INVOICE ID1.6", "INVOICE ID1.7", "INVOICE ID1.8", "INVOICE ID1.9", "INVOICE ID1.10", "INVOICE ID1.11"})
in
#"Split Column by Delimiter"
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |