Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
anshpalash
Helper II
Helper II

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!

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
jennratten
Super User
Super User

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

 

BEFORE (Options 1 and 2)

jennratten_0-1631311302290.png

 

AFTER (Option 1 - Split and Append)

jennratten_1-1631311542442.png

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)

jennratten_2-1631311550579.png

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you! @mahoneypat 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors