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 · Accepted solution]

@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 

[v-hjannapu]

Hi @dannygordon76,

I would also take a moment to thank @Nasif_Azam , @VahidDM for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @dannygordon76,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[v-hjannapu]

Hello @dannygordon76,
I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[Nasif_Azam · Accepted solution]

Hey @dannygordon76 ,

The goal is to dynamically handle any number of month columns across many CSVs, ignore totals, and produce clean data for an Excel PivotTable.

Steps :

1) Combine Files and Promote Headers.

2) Identify your key columns (the 2 columns you keep).

3) Unpivot the rest except known non-month columns (e.g., columns containing “Total”/“YTD”).

4) Create a proper Date from the month label (your Attribute).

5) Filter out rows where the Date couldn’t be created (this excludes leftovers like “Total”).

Load the long table to Excel and build a PivotTable with Date on Columns and your value on Values. (No need to pivot back in Power Query.)

Example M code (drop-in pattern):

let

    // 1) Source & promote headers (replace with your actual Combine Files step)

    Source = Folder.Files("C:\Your\Folder"),

    #"Filtered to CSV" = Table.SelectRows(Source, each Text.EndsWith([Extension], ".csv")),

    #"Added File Content" = Table.AddColumn(#"Filtered to CSV", "FileContents", each Csv.Document(File.Contents([Folder Path] & [Name]),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),

    #"Expanded FileContents" = Table.ExpandTableColumn(#"Added File Content", "FileContents", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),

    // If you already use the built-in "Combine Files" wizard, keep that instead.

    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded FileContents", [PromoteAllScalars=true]),

    // 2) Identify key columns (rename these to your 2 columns)

    KeyCols = {"KeyCol1","KeyCol2"},

    // 3) Build the list of columns to unpivot (exclude keys and obvious non-months like Total/YTD)

    AllCols = Table.ColumnNames(#"Promoted Headers"),

    CandidateMonthCols = List.Select(

        AllCols,

        (cn) => not List.Contains(KeyCols, cn) 

                and not Text.Contains(cn, "total", Comparer.OrdinalIgnoreCase)

                and not Text.Contains(cn, "ytd", Comparer.OrdinalIgnoreCase)

                and not Text.Contains(cn, "grand", Comparer.OrdinalIgnoreCase)

    ),

    #"Unpivoted" = Table.Unpivot(#"Promoted Headers", CandidateMonthCols, "Attribute", "Value"),

    // 4) Create a MonthStart date from "Attribute"

    // Assumes headers like "Jan 24", "Feb 25", etc. Adjust if your labels differ.

    // Uses 'try ... otherwise null' so non-months fall out gracefully.

    #"Added MonthStart" = Table.AddColumn(

        #"Unpivoted", 

        "MonthStart", 

        each

            let

                m = try Date.Month(Date.FromText(Text.Start([Attribute], 3) & " 1", [Culture="en-US"])) otherwise null,

                y = try 2000 + Number.FromText(Text.End([Attribute], 2)) otherwise null

            in

                if m <> null and y <> null then #date(y, m, 1) else null,

        type date

    ),

    // 5) Keep only valid months and non-null values

    #"Filtered to Valid Months" = Table.SelectRows(#"Added MonthStart", each [MonthStart] <> null and [Value] <> null),

    // (Optional) Value type

    #"Changed Type" = Table.TransformColumnTypes(#"Filtered to Valid Months", {{"Value", type number}})

in

    #"Changed Type"

Best Regards,
Nasif Azam

[dannygordon76]

Thank you Nasif. I believe my issue is this. On the files I'm unpivoting, the date was part of the columns, and  3 text columns also existed (as the headers). Power query couldn't resolve what the top row was, so it made no header row. That's a simple solution obviously, but the problem is when I converted the top row as Header. The "original" column 3 became June of 2025, and column 4 became July of 2025 and so on. It tied those months to the original column number so everything was misaligned. To resolvee this I made the dates that came out of my export an undeniable text using a | delimerter between the Month/Year Jun|26. It wasn't my favorite solution, but otherwise every month was tied to a specific column and this will change a lot every month. 

[VahidDM · Accepted solution]

@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. I believe my issue is this. On the files I'm unpivoting, the date was part of the columns, and text 3 text columns also existed (as the headers). Power query couldn't resolve what the top row was, so it made no header row. That's a simple solution obviously, but the problem is when I converted the top row as Header. The "original" column 3 became June of 2025, and column 4 became July of 2025 and so on. It tied those months to the original column number so everything was misaligned. To resolvee this I made the dates that came out of my export an undeniable text using a | delimerter between the Month/Year Jun|26. It wasn't my favorite solution, but otherwise every month was tied to a specific column and this will change a lot every month. 

[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.