Forum Discussion
Power Query Pivoting columns issue
For your Power Query folder setup with pivot/unpivot and the total column issue:
-
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.
-
-
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. -
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.
-
-
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!!
-
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
For your Power Query folder setup with pivot/unpivot and the total column issue:
-
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.
-
-
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. -
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.
-
-
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!!
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.