Forum Discussion

yesanotherOlivi's avatar
yesanotherOlivi
Regular Visitor
1 year ago
Solved

Creating Header and Detail Sequence Numbers to Connect Tables

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

Need Help
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    1 year ago

    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

3 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    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
  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    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
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    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.