Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resolver I
Resolver I

Unpivoting and re-Pivoting creating duplicates

Hi All,


I have the below table in Power BI


 Index Manager First Name   Manager Last Name  HC Aug-23       HC Sep-23     HC Oct-23    HC Nov-23     HC Dec-23       HC Jan-24       HC Feb-24     





The end goal is to create a new calculated column in the above table that sums only columns that are >= current Month/Year (This month it would sum from Nov-23 to Feb -24, Next month it should sum from Dec-23-Feb-24).


I know you cannot reference header dates in powerbi.

In Power Query I have:

1) Renamed the column headers to just dates (removing "HC").

2) Pivoted the data to place the HC columns above (e.g. HC Aug-23 to HC Feb-23) in a column

3) Filtered out past dates in this column.

4) Unpivoted the data.

5) Created a new column in Power Query which sums all columns to the right of "Manager Last Name" 


The issue is when I unpivot the new formed date column the data doesn't go back to it's original structure, some rows seem to be removed.


Can someone please tell me what I'm doing wrong?


Unfortunately it is a condifential dataset so I am unable to share the PBI file.



Super User
Super User

pls try this code



    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZA/D4MgEMW/C7NLK0VdbSdTt26EgSgJpBQSC2n89uWup3V4eXeX3zv+SMlOrGJDtKHYNcaXWUtRF52LLuTQC+xVJXEy6gXAuwvzW0PFd+SPd+gQgcnDBV2s93p6foz3BHMS1C2FWwzBdHST1QbYPqcUAwHNARa0oMEQrBnyvGI2TfZ30PYYQar3GjICYbjcLYdgDq/pSNtHcKbUFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" Index" = _t, #" Manager First Name  " = _t, #" Manager Last Name " = _t, #" HC Aug-23      " = _t, #" HC Sep-23    " = _t, #" HC Oct-23   " = _t, #" HC Nov-23    " = _t, #" HC Dec-23     " = _t, #"  HC Jan-24      " = _t, #" HC Feb-24     " = _t]),
    from = Table.TransformColumnTypes(Source,{{" HC Feb-24     ", type number}}),
    Custom1 = Table.ColumnNames( from),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"KeyWord"}, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [
    t1 = Text.Replace([KeyWord],"HC ",""),
    t2 =  try Date.FromText("1-"&t1,"en-US") otherwise  null,
    t3 =  t2  >= Date.StartOfMonth( Date.From (DateTime.LocalNow()))
    MGoM = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    Custom2 = from,
    #"Added Custom1" = Table.AddColumn(Custom2, "Custom", each List.Sum( List.Transform(
List.Range( Record.FieldValues(_),List.FirstN(
    #"Added Custom1"


@Ahmedx thank you for the reply, however it looks like this is summing all columns that have a value in my table (my dataset is much bigger than in the sample provided). Any idea why it's summing all columns?

write this and show the result


@Ahmedx This returns 77 which is the index for "HC NU Nov23" in the MGoM Table

and try this


@Ahmedx , 23 just gives me the same result as previously, sums all columns with a value.


22, errors, might be due to the fact that I have several columns. the error is "DataFormat.Error: We couldn't convert to Number."




you have a sorting problem and  column names
I can’t help with without the file


pls try again


show this step






show it


show next steps



unfortunately i can't show all the columns but these snippets are from the final step 


This column shows the sum



Below are the columns that need to be summed, If we take the first row above which returns 13 for the sum, we can see below that the sum should be zero, I think what's happening is that it's summing all columns with a value as I have a column called "FY NU" which has 12 and the index column for this row is 1 which gives us 13







pls try this

List.Sum( List.Transform(
List.Range( Record.FieldValues(_),
MGoM[Index]{0},MGoM[Index]{ List.Count(MGoM[Index])-1}),Number.From))



@Ahmedx Thank you so much for all your efforts but unfortunately this doesn't work either, not sure why 

you can only give two lines to see what the problem is

everything is correct, but what should not be summed by which column?

I can’t say , but it may be due to
1) you are not sorting the column by ascending date.
can you show a screenshot of your table?

Resolver I
Resolver I

@MFelix Thank you for the response.


To answer your questions first:

- I want to sum the rows so group by isn't ideal.


I ran into the same issue with the solution you provided as I did with my first attempt, After executing th esteps you provided I'm left with 85 rows removed (original data had 3,294 rows, after executing the steps I have 3209).


Not sure why this is or how to overcome it, the index column is unique, I thought this might mitigate the issue. 




Hi @obriaincian ,


If you do the sum after the unpivot and use the index on your calculation you will get the sum of the rows that you need.


On my case I get the same number of rows has you can see that are 6 rows.


I believe that you are losing columns because you refer that you are filtering information so when you do that you may have some columns that don't have data for the values thar remain so you loose values.


Can you please tell me based on the example you provided what is the value you would get in the end?


Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.