Forum Discussion

509fg's avatar
509fg
New Member
4 months ago
Solved

Restoring a deleted column in power query without redoing all steps

Hi everyone, I’m fairly new to Power Query and ran into an issue during data cleaning. After applying several steps, I realized that I need a column I had previously deleted earlier in the process. ...
  • pcoley's avatar
    4 months ago

    509fg you don´t need to rebuild "all the steps", just those steps where you removed, reordered or expanded tables.
    Please try going back to the step where you deleted the column and ajust the script as needed:
    for example, in this step i deleted "Col1" and "col2", if i dont want to erase one of those i just need to remove that column from the list. 

    if i need to have col2 back then the sentence would be:
    = Table.RemoveColumns(#"Sorted Rows",{"Col1"})

    If you deleted the column using "removeother columns", then just go to the setting of that step and adjust as needed: 

    Make sure that if you reordered the columns then you have to include the deleted column in the sorted list. And if you have other steps removing columns, you need to do the adjustment of the lists of columns removed or kept. 


  • SundarRaj's avatar
    4 months ago

    Hi 509fg

     

    An interesting trick to pull out the required column after doing a lot of steps is using a few functions together.
    Table.AddIndexColumn.

    Table.TransformColumns.

     

    Assuming you are pulling a column named "Sum" from Step2 which was deleted in Step3, while being in Step7 now.
    At Step7, we can write this code which will directly pull out the column with no previous steps required.
    = Table.TransformColumns ( Table.AddIndexColumn ( Step7 , "Index" , 0 , 1 ) , { "Index" ,  each Step2 [Sum] { _ } } )

    What this code essentially will do is pick out the row position in Step2 as per our Index column created and populate the respective 'Sum" values in the new column. 
    A very important point to note: The RowCount in the previous step (Step2) that we are referring to extract the column should necessarily be the same rows as in the Step7.

    Happy to connect and discuss more on this if this isn't clear on my part. Also, if a small reference file would be beneficial, please let me know.

    Thanks,