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 t...
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
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