Merge or concatenate values in Top 2 Rows into a single Row to Promote as Headers

Hi v-frfei-msft, Nathaniel_C ,

Thank you all for your valuable inputs. :-)

Last night after posting, i gave a last try and came up with this solution. Though it is long, it does serve my purpose.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLdSsNAEIVfpQS8q5vdmUncvczPtgRsI41plZCLoEFFrGL7/jgTi0nBq0I453DIfrNk0jRBMP/nqfdvx8PErzZ99/QatPMmWL0PTd5/HdmqrBP71ap/YU2/u/2znL3LWGm2C/3M6FDHobkZCxNqPCsg1EYKmZGkcvTw+dHLGHPKPETioixztq1fLgtfcXrYJRs2dOjIISdAMMrIu7G1qEg6csrg5fDIUewIBASgUAJwiImDM8rS5Wi+s7XDHSEiVEinLQgwy/0ECCNQYuq3rOtyfc1M+fyJqI7Ikca/VcIAyv1iAsIRJLG+L25lpUmxeWRf1QOHIWAjPf4WbfsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    
    // take first 2 rows and transform them
    First2Rows = Table.FirstN(Source, 2),
    TransposeRows2Columns = Table.Transpose(First2Rows),
    AddCustomColumn = Table.AddColumn(TransposeRows2Columns, "Custom", each if [Column1] = "" then [Column2] else [Column1]&"~"&[Column2]),
    RemoveFirst2Columns = Table.RemoveColumns(AddCustomColumn,{"Column1", "Column2"}),
    TransposeColumn2Rows = Table.Transpose(RemoveFirst2Columns),
    SourceWithout2FirstRows = Table.RemoveFirstN(Source, 2),
    CombineRows2Source = Table.Combine({TransposeColumn2Rows,SourceWithout2FirstRows}),
    PromotedFirstRowAsHeaders = Table.PromoteHeaders(CombineRows2Source, [PromoteAllScalars=true])
in
    PromotedFirstRowAsHeaders

The Result is how i expect it:

Note: I am only transposing the 1st 2 rows as the dataset is huge and it will exceed 16384 columns if i transpose all. Besides, it is also going to hog my memory.

A few observations of PowerBI:

1. Can the steps be shortened or is there a more efficient way of doing this? I have more steps following these steps to do further transformations.
2. Right now, i am considering only 1st 2 rows. But what if one of the data extracts has more number of null rows on top and some cells may contain text in it? I think the code would fail to determine the Dimensions and Facts columns correctly.
3. I cannot auto-resize the columns though it does show the resize arrows.
4. I am unable to enter null values in cells in Power BI > Enter Data.

Can someone address these questions inorder to have a concise, dynamic and efficient solution? I am here to learn!