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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Lookup to display duplicate in columns

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

CustomerIDInvoiceID
1234465425
1234465426
1234365298
1234435029

 

I need help so that I can see the next InvoiceID that is linked to that customer as a coloumn: 

Example:

CustomerIDInvoiceIDInvoiceID2
123446542565426
1234365298 
1234435029 

 

Please could you help me in creating a dax or something??

 

Thank You

Viral

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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 🙂

YouTube  LinkedIn



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
Super User

@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 🙂

YouTube  LinkedIn



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.