Power Query Pivoting columns issue

[dannygordon76]

I have a folder I'm pointing to in Power Query. There's 2 columns I need, and then there can be a series of other columns depending on the file (it's showing months, the file is in CSV, but it could be any number of months). Because new files will be added all the time, I've built out a "Builder" file that has 10 years worth of Months. I'm doing 4 steps in Power Query (it is excel, not power bi):

* Using the first row as a header
*   Choosing the columns I want to keep, and un-pivoting all the other columns. 

* After I do this, i'm creating a custom column to get the Date I need. "

#date(2000 + Number.FromText(Text.End([Attribute], 2)), Date.Month(Date.FromText(Text.Start([Attribute], 3) & " 1")), 1)" 

* Pivoting the column called Attribute to make that my actual columns

All of this works out perfect and when I build a pivot table I'm able to get the data I need. However, the last month of what I have (after I just filter on new files and not the builder file) is taking from a total column. I've deleted the column and it's still just pulling in the total. 

My question is, is this the best way to do this? There will be several files added to the folder from several users, and the date ranges can be different. So it's tricky, I don't want us all to have to build a data model every time. I can attach the two spreadsheets (csv) where I'm getting the issue if that's helpful. 

[VahidDM]

@dannygordon76 

For your Power Query folder setup with pivot/unpivot and the total column issue:

1. Your approach is correct:

   • Use first row as header.

   • Keep your key columns (like ID, Category, etc.).

   • Unpivot all other columns (the month columns).

   • Build a date from the attribute text.

   • Pivot back if needed.

2. Why the "total" is sneaking in:
   Even if you delete the total column in one file, when new CSVs land in the folder, Power Query re-detects all columns, so the “total” header might still appear. That’s why it keeps showing up after unpivot.

3. Best practice fix:

   • After unpivot, filter out rows where the Attribute = "Total" (or whatever your total column name is).

   • Do this before you build the date column.

   • This way, even if new files come in with a total column, it won’t flow downstream.

4. Alternative approach:
   Instead of pre-building 10 years of months, you can just let PQ unpivot everything dynamically and then filter out invalid months or totals. This is easier to maintain because you won’t have to manage a big “builder” table.

So your process is fine — just add a row filter step after unpivot to exclude “Total” (or any non-month headers). That way, only valid months remain, and new files won’t break it.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[dannygordon76]

Thank you VAhidDM for the response, I appreciate it. All really good points. I did actually filter out the Total (and an Average) column before I unpivoted all Other columns. It took the values from that total column and puts it in the last Month for some reason. It's really bizar. I've went in and re-built it a few times and it still does it.