Re-adding a previously removed column in power query

rjcampbell

New Member
Joined
Jan 29, 2017
Messages
1
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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"
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top