Forum Discussion

anshpalash's avatar
anshpalash
Icon for Helper II rankHelper II
5 years ago
Solved

Column Manipulation

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   Ess...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 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
Icon for Super User rankSuper User
5 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