Hi @harishrathore,
Paste this into a new blank query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVJNa4QwEP0r4mkXVpiYTaPHqvRQugjbvckeAhtQUCOaUuyvb8bP1HYLC0PmzQdv5o1mmese3ERoadylb9AlstNFLXShakymqTPVjb0XX4jiqDNvpMtuG0x2PWQuMeD57QwAPqVPfPeyR3LvknoJVspP0TqJbJRe+lhIg6OJTr0Tq6oRdY8jc1E1SiEbAfOADcIxgtnBMBmLzIPAI+G4T5SLVujmYx1IwQO+1ENwqnJnCJxIaV3Kbr9KsQ3JfeQ7nYEA8X1gG1mRaCu5jhkbuekkd3QhHZvlLOBIMc8HXZTZugJb16u65baqn6pJgLLuK/mdnnbCbxBMm8ye+8OdRrjuszHK2YMjY1XfCvzXZEv+PsWACBlGM/sWG+PsH7nXbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#" = _t, #"TOS Number" = _t, TOO = _t, #"From Depot" = _t, #"TOO Number" = _t, #"Supplier Name" = _t, #"Brand Name" = _t, #"TOO Qty" = _t, #"(blank)" = _t, #"TOS Qty" = _t, #"(blank).1" = _t, #"Qty BL" = _t, Rate = _t, Amount = _t]),
DemoteHeaders = Table.DemoteHeaders(Source),
FillDown = Table.FillDown(DemoteHeaders,{"Column1"}),
AddIndex = Table.AddIndexColumn(FillDown, "Index", 0, 1, Int64.Type),
AddGroupKey = Table.FillDown( Table.AddColumn(AddIndex, "MOD", each if Number.Mod([Index], 2)=0 then [Index] else null ), {"MOD"} ),
GroupRows = Table.Group( AddGroupKey, {"MOD"}, {{"AllRows", each _, type table [Column1=any, Column2=nullable text, Column3=nullable text, Column4=nullable text, Column5=nullable text, Column6=nullable text, Column7=nullable text, Column8=nullable text, Column9=nullable text, Column10=nullable text, Column11=nullable text, Column12=any, Column13=any, Column14=any, Index=number, MOD=number]}}),
TransformTable = Table.AddColumn(GroupRows, "Custom", each Table.Transpose( Table.UnpivotOtherColumns( Table.RemoveColumns( [AllRows], { "Index", "MOD" } ), {"Column1"}, "Attribute", "Value")[[Value]]))[[MOD], [Custom]],
ExpandTable = Table.ExpandTableColumn(TransformTable, "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}),
PromoteHeaders = Table.PromoteHeaders(ExpandTable, [PromoteAllScalars=true]),
ReplaceEmptyValue = Table.ReplaceValue(PromoteHeaders,"",null,Replacer.ReplaceValue,{"0", "TOS Number", "TOO", "From Depot", "TOO Number", "Supplier Name", "Brand Name", "TOO Qty", "(blank)", "TOS Qty", "(blank).1", "Qty BL", "Rate", "Amount", "Date", "Type", "Destination", "TOO Date", "", "Size", "CBs", "Btls", "CBs_1", "Btls_2"}),
FillDownAll = Table.FillDown(ReplaceEmptyValue,{"0", "TOS Number", "TOO", "From Depot", "TOO Number", "Supplier Name", "Brand Name", "TOO Qty", "(blank)", "TOS Qty", "(blank).1", "Qty BL", "Rate", "Amount", "Date", "Type", "Destination", "TOO Date", "", "Size", "CBs", "Btls", "CBs_1", "Btls_2"}),
RenameColumns = Table.RenameColumns(FillDownAll,{{"TOO", "TOO Type"}, {"Date", "TOS Date"}, {"Destination", "To Depot"}, {"TOO Qty", "TOO Qty CBs"}, {"(blank)", "TOO Qty Btls"}, {"TOS Qty", "TOS Qty CBs"}, {"(blank).1", "TOS Qty Btls"}}),
RemoveColumns = Table.RemoveColumns(RenameColumns,{"0", "Type", "", "CBs", "Btls", "CBs_1", "Btls_2"}),
ChangeType = Table.TransformColumnTypes(RemoveColumns,{{"TOS Number", type text}, {"TOO Type", type text}, {"From Depot", type text}, {"TOO Number", type text}, {"Supplier Name", type text}, {"Brand Name", type text}, {"TOO Qty CBs", Int64.Type}, {"TOO Qty Btls", Int64.Type}, {"TOS Qty CBs", Int64.Type}, {"TOS Qty Btls", Int64.Type}, {"Qty BL", Int64.Type}, {"Rate", Int64.Type}, {"Amount", Int64.Type}, {"TOS Date", type date}, {"To Depot", type text}, {"TOO Date", type date}, {"Size", type text}})
in
ChangeType
.
The key is seperating the sets of rows
And transforming them into a single line with a combination of UnpivotOtherColumns, SelectColumns and Transpose steps
With this result
Here’s a sample file. eDNA - adv transform.pbix (59.9 KB)
I hope this is helpful.
