Forum Discussion
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...
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_HeaderSeqNoDetail
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
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.