Results 1 to 2 of 2

Thread: Re-adding a previously removed column in power query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re-adding a previously removed column in power query

    HI,

    I am pulling products through from microsoft crm using the odata feed. I have over 350 fields been pulled through and for any particular power query i am creating i will use about 10 of these fields.

    The problem i am having is that at times i will remove a column that i will later want to use.

    Is there a process to add an individual column row that has been removed in a previous applied step? My experience with changing previous steps is that it corrupts all of the subsequent steps.

    I am new to power query so hope i have explained myself Clearly. Any questions to make my problem clearer are welcome.

    Thanks.

  2. #2
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Re-adding a previously removed column in power query

    Welcome to the MrExcel forum.

    Below some code with 3 options. I created a 7-column table in Excel *, then I selected 4 columns and added a sum.
    * I guess it should be working similarly with your Odata feed.

    I added the first line of each option via the Advanced Editor.
    The "Expanded Column" steps were done via the UI.

    These options will only work if the rows all still the same (so no filtering, sorting, grouping in between, otherwise you would need (a) column(s) with key values and merge the tables from the different steps).

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Column 3", Int64.Type}, {"Column 4", Int64.Type}, {"Column 5", Int64.Type}, {"Column 6", Int64.Type}, {"Column 7", Int64.Type}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column 1", "Column 2", "Column 3", "Column 4"}),
        #"Inserted Sum" = Table.AddColumn(#"Removed Other Columns", "Sum", each List.Sum({[Column 1], [Column 2], [Column 3], [Column 4]}), Int64.Type),
    
    // 1. Add an additional column from "Changed Type", ignoring the "Inserted Sum"
        #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"Column 1", "Column 2", "Column 3", "Column 4", "Column 5"}),
    
    // 2. Add an additional column from "Changed Type", ignoring the previous steps
        AddColumn5 = Table.FromColumns({Table.ToRecords(#"Inserted Sum"),#"Changed Type"[Column 5]},{"Column1", "Column 5"}),
        #"Expanded Column1" = Table.ExpandRecordColumn(AddColumn5, "Column1", {"Column 1", "Column 2", "Column 3", "Column 4", "Sum"}, {"Column 1", "Column 2", "Column 3", "Column 4", "Sum"}),
         
    // 3. Combine "Inserted Sum" with "Changed Type", then select columns
        AllColumns = Table.FromColumns({Table.ToRecords(#"Inserted Sum"),Table.ToRecords(#"Changed Type")}),
        #"Expanded Column2" = Table.ExpandRecordColumn(AllColumns, "Column1", {"Column 1", "Column 2", "Column 3", "Column 4", "Sum"}, {"Column 1", "Column 2", "Column 3", "Column 4", "Sum"}),
        #"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column2", {"Column 6", "Column 7"}, {"Column 6", "Column 7"})
    
    in
        #"Expanded Column3"
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •