Creating Header and Detail Sequence Numbers to Connect Tables

[yesanotherOlivi]

Hi! I’m working in Power Query right now and I’m trying to connect two tables that don’t have any keys. I want to make some, but I’m having a hard time with indexes. I’d like to create an index for the header table and two indexes for the detail table. The header table’s index should only increase when the date changes, and should reset for each new invoice. One of the detail’s indexes should be the same as the header, and the other should increment with each individual product row.

HEADER                              

Journal Number          Posting Date    HeaderSeqNo 

123                              1/20/2025                    0         

123                              1/21/2025                    1         

456                              1/22/2025                    0         

456                              1/23/2025                    1         

456                              1/24/2025                    2         

DETAIL                                 

Journal No      PostingDate     HeaderSeqNo  DetailSeqNo

123                  1/20/2025                    0                      0

123                  1/20/2025                    0                      1

123                  1/21/2025                    1                      0

123                  1/21/2025                    1                      1

[v-nmadadi-msft]

Hi @yesanotherOlivi,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[dufoq3]

But, maybe you can just merge tables based on Journal No and PostingDate like this:

let
    HeaderTbl = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ3MgAiI1Mg21EpVgdJ3BAm7gQWNzE1g4gbwcSdUcWNYeIuqOImMHFXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Journal Number" = _t, #"Posting Date" = _t, Customer = _t]),
    DetailTbl = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ3MgAiI1MgO6AoP6U0uUTBUClWB5+8Eaq8IQH9GPJA/bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Journal No" = _t, PostingDate = _t, Product = _t]),
    MergedQueries = Table.NestedJoin(DetailTbl, {"Journal No", "PostingDate"}, HeaderTbl, {"Journal Number", "Posting Date"}, "HeaderTbl", JoinKind.LeftOuter),
    ExpandedHeaderTbl = Table.ExpandTableColumn(MergedQueries, "HeaderTbl", {"Customer"}, {"Customer"})
in
    ExpandedHeaderTbl

[dufoq3 · Accepted solution]

Hi @yesanotherOlivi, check this:

Header

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ3MgAiI1OlWB0kMUOEmImpGUTMCIuYMRYxE6hYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Journal Number" = _t, #"Posting Date" = _t]),
    Ad_HeaderSeqNo = Table.Combine(Table.Group(Source, {"Journal Number"}, {{"T", each Table.AddIndexColumn(_, "HeaderSeqNo", 0, 1, Int64.Type), type table}}, 0)[T])
in
    Ad_HeaderSeqNo

Detail

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ3MgAiI1OlWB0CYoZ4xGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Journal No" = _t, PostingDate = _t]),
    Ad_SeqNumbers = Table.Combine(Table.AddColumn(Table.AddIndexColumn(Table.Group(Source, {"Journal No", "PostingDate"}, {{"T", each Table.AddIndexColumn(_, "DetailSeqNo", 0, 1, Int64.Type), type table}}, 0), "HeaderSeqNo", 0, 1, Int64.Type), "T2", (x)=> Table.AddColumn(x[T], "HeaderSeqNo", (y)=> x[HeaderSeqNo], Int64.Type), type table)[T2])
in
    Ad_SeqNumbers