locked
Power Query - Combine 6 Columns into 2 - Repeating Header RRS feed

  • Question

  • hi friends,

    how do I combine 6 columns into 2

    In the image 1 have 3 repeating columns  known as ID and then 3 columns as description.



    How can I make 1 column   ID and 1 Column Description.

    I looked at all the buttons on power query but nothing to make them into 2 columns


    I have looked everywhere and, I don't know how to do this :(

    any tips


    Cheers Dan

    Friday, July 22, 2016 1:00 AM

Answers

  • Yes, your code needs to look like this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID2", type text}, {"Description2", type text}}),
        Append = Table.Combine({
    
    Table.SelectColumns(Source,{"ID", "Description"}), 
    
    Table.RenameColumns(Table.SelectColumns(Source,{"ID2", "Description2"}),{{"ID2", "ID"}, {"Description2", "Description"}}), 
    
    Table.RenameColumns(Table.SelectColumns(Source,{"ID3", "Description3"}),{{"ID3", "ID"}, {"Description3", "Description"}})
    
    })
    
    in
        Append


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Dan_CS Friday, July 22, 2016 5:02 PM
    Friday, July 22, 2016 4:57 PM

All replies

  • Hi Dan,

    this is a bit of a monster, but if it's just these 3 pairs of columns it's probably the easiest way to do:

    Table.Combine({

    Table.SelectColumns(Source,{"ID", "Description"}),

    Table.RenameColumns(Table.SelectColumns(Source,{"ID2", "Description2"}),{{"ID2", "ID"}, {"Description2", "Description"}}),

    Table.RenameColumns(Table.SelectColumns(Source,{"ID3", "Description3"}),{{"ID3", "ID"}, {"Description3", "Description"}})

    })

    So what you do here is to cut your table into 3 by using Table.SelectColumns. Then you append them (Table.Combine). But to make them appear under each other, the column names need to be the same - so you rename them as well (except the first).


    Imke Feldmann TheBIccountant.com

    Friday, July 22, 2016 4:12 PM
  • Hello Imke,

    thank you for helping :)

    I was completely surprised, I thought Power Query would be able to just stack columns vertically like I was attempting to.

    But after 2 days and no success, and nothing on google, I wasn't sure what to do next.


    If its easier I can just call the columns

    ID |  Description |   ID | Description  | ID | Description

    I entered the code

    I may have entered it incorrectly?


    Cheers Dan

    Friday, July 22, 2016 4:50 PM
  • Yes, your code needs to look like this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID2", type text}, {"Description2", type text}}),
        Append = Table.Combine({
    
    Table.SelectColumns(Source,{"ID", "Description"}), 
    
    Table.RenameColumns(Table.SelectColumns(Source,{"ID2", "Description2"}),{{"ID2", "ID"}, {"Description2", "Description"}}), 
    
    Table.RenameColumns(Table.SelectColumns(Source,{"ID3", "Description3"}),{{"ID3", "ID"}, {"Description3", "Description"}})
    
    })
    
    in
        Append


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Dan_CS Friday, July 22, 2016 5:02 PM
    Friday, July 22, 2016 4:57 PM
  • Thank you so much Imke,

    I was getting a bit disheartened, because this seemed like a simple task , and no amount of googling or trying this and trying that helped.

    This will save me so much time in the future - no more copy and pasting huge excel columns.

    Thank you again - the code will help me make more queries

    Have a wonderful evening

    :)  :)


    Cheers Dan

    Friday, July 22, 2016 5:02 PM
  • Dan,

    If the columns are variable, a general solution would require even more work. For example:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Description", type text}, {"ID2", type text}, {"Description2", type text}, {"ID3", type text}, {"Description3", type text}}),
        AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1),
        UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"Index"}, "Attribute", "Value"),
        AddedCustom = Table.AddColumn(UnpivotedOtherColumns, "Attribute2", each if Text.Contains([Attribute], "ID") then "ID" else "Description"),
        RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"Value", "Attribute2"}),
        AddedIndex1 = Table.AddIndexColumn(RemovedOtherColumns, "Index", 0, 1),
        PivotedColumn = Table.Pivot(AddedIndex1, List.Distinct(AddedIndex1[Attribute2]), "Attribute2", "Value"),
        RemovedOtherColumns1 = Table.SelectColumns(PivotedColumn,{"ID", "Description"}),
        RemovedNulls1 = List.RemoveNulls(RemovedOtherColumns1[ID]),
        RemovedNulls2 = List.RemoveNulls(RemovedOtherColumns1[Description]),
        NewTable = Table.FromColumns({RemovedNulls1, RemovedNulls2}, {"ID", "Description"})
    in
        NewTable
    On the other hand, Imke's solution is perfect if the columns are fixed.

    Friday, July 22, 2016 5:14 PM
  • Thank you for this extensive solution as well Colin,

    I will incorporate it into one of the tasks - I am attempting  -  which has  a few more columns.

    :)

    Cheers Dan

    Friday, July 22, 2016 5:20 PM