Dynamic Pivot Columns with a Total Column

Question

I have a Data Flow that pivots rows into columns. The columns of the pivot are dynamic, so a new column is created if there is a new row value automatically. What I want to do is dynamically also add a column that SUMS the pivot columns to create a TOTAL. Is this possible in a Data Flow?

Answer 1

Hi @DerekHorrall-5611,

Welcome to Microsoft Q&A Platform.

Below is one approach to implement the above requirement. I have used 2 sources in dataflow - one with pivoted and one is the original file as you mentioned to achieve this.

Used below transformations -

• Aggregate - This transformation is to summarize on original source before unpivoting to generate Total
• Join - This transformation joins the above aggregate output with pivoted source data based on ID and adds Total field

Attaching the GIF showing the same and source files to the answer.

Hope this helps!