Forum Discussion

anshpalash's avatar
anshpalash
Helper II
4 years ago
Solved

Column Manipulation

Hi,

 

I have a table similar to format:

MakeHondaSale123
Year2016StateTX

 

I want to convert it to format:

MakeHonda
Year2016
Sale123
StateTX

 

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!

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    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

  • jennratten's avatar
    jennratten
    Super User
    4 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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