Combine 2 rows into 1

pls try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJBIJ/85MQcBefSoqLUvORKoIBjbn5pXgmQEZJfkpijFKsTrZQG5KUhawKh2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Client = _t, State = _t, Country = _t, Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Client", type text}, {"State", type text}, {"Country", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    from =  Table.ColumnNames(#"Changed Type"),
    NM1 = List.Skip(from, (x)=> x<>"Column1"),
    NM2 = List.Skip(Table.ToRows(#"Changed Type"){0},(x)=>x<>"Local Currency"),
    final =Table.RenameColumns(#"Changed Type" , List.Zip({NM1,NM2})),
    #"Removed Top Rows" = Table.Skip(final,1)
in
    #"Removed Top Rows"

Hi,

I have solved a similar question in the attached file.

Hope this helps.

You can create an applied step that combines the first two rows, assuming you haven't promoted any headers yet.

= let
        FirstTwoRows = Table.FirstN(Source, 2),
        Renames =
            List.Transform(
                Table.ToColumns(FirstTwoRows),
                each Text.Combine(List.Transform(_, Text.From), "")
            )
    in Renames

The use the original column and the new names in a rename operation.

Skip the first two rows after

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sqVTSUXLOyUzNKwEygksSS1JBAvmleSVFICkoitWJVkLwdJR88pMTcxScS4uKUvOSQeocc0FaFICskPySxBywhgogAAoQS9FJSywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    NewHeaders = 
    let
        FirstTwoRows = Table.FirstN(Source, 2),
        Renames =
            List.Transform(
                Table.ToColumns(FirstTwoRows),
                each Text.Combine(List.Transform(_, Text.From), "")
            )
    in Renames,
    CombinedHeaders = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), NewHeaders})),
    #"Removed Top Rows" = Table.Skip(CombinedHeaders,2)
in
    #"Removed Top Rows"

You can apply this logic from Step 2. Validated and it works if using your sample data. 

let
    Source = Source,
    Header2 = Table.FirstN(Source, 2),
    Row1 = Record.FieldValues(Header2{0}),
    Row2 = Record.FieldValues(Header2{1}),

    NewHeaders =
        List.Transform(
            List.Zip({Row1, Row2}),
            each Text.Combine(
                List.Select(
                    List.Transform(_, (v) => if v = null then "" else Text.Trim(Text.From(v))),
                    (t) => t <> ""
                ),
                " "
            )
        ),


    HeaderRowTable = Table.FromRows({NewHeaders}),
    DataRows = Table.Skip(Source, 2),

    Combined = Table.Combine({HeaderRowTable, DataRows}),
    Promoted = Table.PromoteHeaders(Combined, [PromoteAllScalars=true])
in
    Promoted

that doesnt work for some reason is it possible to do it in transform data?

Please see above. I actually need I think need to promote headers first? The above solutions are not working. Do I need to promote headers first and then do something? I basically need the below in 1 row. 

Hi homboy27,

I would also take a moment to thank danextian  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.