Column Manipulation

[anshpalash]

Hi,

I have a table similar to format:

Make	Honda	Sale	123
Year	2016	State	TX

I want to convert it to format:

Make	Honda
Year	2016
Sale	123
State	TX

Essentially, need to move 3rd column at bottom of 1st column and move 4th column at bottom of 3rd column.

I would really appreciat if someone could provide any tips on how to achieve this. Thank you!

[watkinnc]

Here's a way to do it but splitting the table into a list of 2 row tables:

Custom2 = Table.Split(Source, 2),
#"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.Combine({Table.FirstN([Column1], 2)[[Column1], [Column2]], Table.LastN(Table.RenameColumns([Column1][[Column3], [Column4]], {{"Column3", "Column1"}, {"Column4", "Column2"}}), 2) [[Column1], [Column2]]})),

#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Custom.Column1", "Custom.Column2"})
in
#"Expanded Custom"

--Nate

[jennratten]

Hello - there are a few different ways to accomplish this.  Below are two options.  

BEFORE (Options 1 and 2)

AFTER (Option 1 - Split and Append)

Option 1 SCRIPT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikxNLFLSUTIyMDQDUsEliSWpQDokQik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Make = _t, Honda = _t, Sale = _t, #"123" = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Make", type text}, {"Honda", Int64.Type}, {"Sale", type text}, {"123", type text}}),
    DemoteHeaders = Table.DemoteHeaders(ChangeType),
    Table1 = Table.SelectColumns ( DemoteHeaders, { "Column1", "Column2"} ),
    Table2 = Table.RenameColumns ( 
        Table.SelectColumns ( DemoteHeaders, { "Column3", "Column4"} ),
        { { "Column3", "Column1"}, { "Column4", "Column2" } } 
    ),
    newTable = Table.Combine ( { Table1, Table2 } )
in
    newTable

AFTER (Option 2 - Construct from Lists)

Option 2 Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikxNLFLSUTIyMDQDUsEliSWpQDokQik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Make = _t, Honda = _t, Sale = _t, #"123" = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Make", type text}, {"Honda", Int64.Type}, {"Sale", type text}, {"123", type text}}),
    DemoteHeaders = Table.DemoteHeaders(ChangeType),
    RowsToRecords = Table.Group(
        DemoteHeaders, 
        "Column1", 
        {"Lists", each Table.ToRows( _ ) }
    ),
    CombineLists = List.Combine ( { RowsToRecords[Lists]{0}{0}, RowsToRecords[Lists]{1}{0} } ),
    // Construct a new table
    newTable = #table (
        // column names
       { "Column1", "Column2", "Column3", "Column4" },
       { 
        // record 1 values
           List.Alternate ( CombineLists, 1, 1, 1),
        // record 2 values
           List.Alternate ( CombineLists, 1, 1) 
        }
    ),
    Transpose = Table.Transpose(newTable)
in
    Transpose

[mahoneypat · Accepted solution]

That format isn't the best for analysis but here's one way to do it in the query editor with a column that has a custom list of records.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MTlXSUfLIz0tJBNLBiTkgrqGRsVKsTrRSZGpiEZBrZGBoBpIsSSwByYZEKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {[Col1 = [Column1], Col2 = [Column2]], [Col1 = [Column3], Col2 = [Column4]]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Col1", "Col2"}, {"Col1", "Col2"})
in
    #"Expanded Custom1"

Pat

[anshpalash]

Thank you! @mahoneypat