Deleting and Renaming Columns using a predefined list

View attachment samplepandl.xlsxView attachment cleanuplist.xlsx
I have the file attached named "samplepandl.xlsx" (I wasn't able to load the CSV file I usually get this data in) that contains P&Ls for a list of Jobs and they total at the end. The job numbers are listed on the first row of each column. I have no problems transforming it to get rid of the columns that are blank and I also keep the last column, RA, to test. I then transform back and I have to delete and rename a set of columns as defined in the "cleauplist.xlsx." The cleanup list is a list of columns that I need to delete and those columns that I need to rename so the the TOTAL of all of these columns across match the TOTAL in the last column (I test that outside of PQ). Right now, I manually go in the editor each month and adjust (delete more columns and add more renames) as more jobs are added. What I need some help figuring out a way to use the Table.RemoveColumns and Table.RenameColumns function to point these list(s) where they reside in a separate tab, spreadsheet or CSV (since those load faster). I imagine I could try to simulate the delete columns using a "left outer join," but the columns that need to be renamed using a similar technique is beyond me. Thanks.

Hi,

See if this does what you need...

Note that the template refers to your two workbooks in the following path:

• = Excel.Workbook(File.Contents("C:\Users\Public\Downloads\samplepandl.xlsx"), null, true)
• = Excel.Workbook(File.Contents("C:\Users\Public\Downloads\cleanuplist.xlsx"), null, true)

Either save your source files to this same path, or open the template and edit the source step to refer to the path where your source files are.

View attachment Cleanup Template.xlsx

Great start. I see what you did. You used this to eliminate the column renaming step:

= Table.ReplaceValue(#"Filtered Rows2","Total ","",Replacer.ReplaceText,{"Column1"})

I can't do that. See the two items circled in the pic above. The first one has to be renamed because it's on my list. The second one, not on my list to be renamed, has to be deleted. There are several of these...they are all subtotals and must be deleted. All of the ones with "null" must be deleted...except the FIRST and LAST row that are not labeled until your last step:

= Table.RenameColumns(#"Remove Columns",{{List.First(Table.ColumnNames(#"Remove Columns")),"Description"},{List.Last(Table.ColumnNames(#"Remove Columns")),"TOTAL"}})

End the end a sum of all of the columns, except the Description and TOTAL column, must equal the amount shown in the TOTAL column...otherwise I have too many columns or deleted the wrong one.

Ah, I think I understand.

Try this new version, attached.

Note: The source step in the queries still refer to the path in my first reply above. Edit if needed.

You will see that I split the Cleanup table into a DELETE table and a RENAME table. The rename table (the way I'm using it to iterate through and rename the columns in Data) cannot contain nulls (blank rows) which will affect the rename process.

Make sure the table contains ALL the items to delete (including the column names of sub totals eg: Total FU France, Total Paris, etc**...) and also ALL the items to rename (with their corresponding new name).

** Note: These are not in the Cleanup table currently, so do not yet get deleted from the result.

Basically,

• if you have a column name in the DELETE column in table Cleanup, that will delete the column in Data
• if you have a column name in the RENAME column (and a corresponding NEWNAME) in table Cleanup, that will rename accordingly in Data

View attachment Cleanup Template.xlsx

:thumb::thumb::thumb::thumb: Thank you very much. Your help got me going in the right direction. I did a little M editor jujitsu to avoid having to list all of those other "Total" lines that have be deleted. I knew from experience that renaming of the 1st and last column needed to happen right away, otherwise, they stay as nulls and are easy to delete. I also knew I didn't need "Column2" after the first transpose (sorry, should have told you that). In addition, due to experience, I knew that one once the Table.RenameColumns and Table.RemoveColumns fired off I could demote the headers, transpose and then take care of the remainder of the "Total" lines without having to do what you suggested.


let
Source = Excel.Workbook(File.Contents("c:\samplepandl.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="samplepandl",Kind="Sheet"]}[Data],
#"Promoted Headers3" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Rename Outer Columns" = Table.RenameColumns(#"Promoted Headers3",{{List.First(Table.ColumnNames(#"Promoted Headers3")),"Description"},{List.Last(Table.ColumnNames(#"Promoted Headers3")),"TOTAL"}}),
#"Demoted Headers2" = Table.DemoteHeaders(#"Rename Outer Columns"),
#"Transposed Table" = Table.Transpose(#"Demoted Headers2"),
#"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers1",{"Column2"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each not Text.Contains([Description], "Column")),
#"Demoted Headers" = Table.DemoteHeaders(#"Filtered Rows"),
#"Transposed Table1" = Table.Transpose(#"Demoted Headers"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Rename Columns" = Table.RenameColumns(#"Promoted Headers", Table.ToRows(tblRename)),
#"Remove Columns" = Table.RemoveColumns(#"Rename Columns",tblDelete[DELETE]),
#"Demoted Headers1" = Table.DemoteHeaders(#"Remove Columns"),
#"Transposed Table2" = Table.Transpose(#"Demoted Headers1"),
#"Filtered Rows1" = Table.SelectRows(#"Transposed Table2", each not Text.Contains([Column1], "Total")),
#"Transposed Table3" = Table.Transpose(#"Filtered Rows1"),
#"Promoted Headers2" = Table.PromoteHeaders(#"Transposed Table3", [PromoteAllScalars=true])
in
#"Promoted Headers2"

Excellent!!
Glad to hear your extra M-Code jujitsu got you right. :rockon: