Unpivoting and re-Pivoting creating duplicates

[obriaincian]

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
1	John	Coomey	3	2	5	2	3	6	5
2	Mary	Lindsay	4	6	5	3	6	9	6
3	Tina	Blackwell	5	4	4	5	8	3	8
4	Michael	Button	3	7	8	3	6	4	7
5	Judy	Mitchell	2	5	6	6	3	6	6
6	Mia	Dunne	4	6	9	9	5	2	4

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.

Thanks

[Ahmedx]

pls try this code

let
    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()))
][t3]),
    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(
MGoM[Index],1){0},List.LastN(
MGoM[Index],1){0}),Number.From)))
in
    #"Added Custom1"

[obriaincian]

@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?

[Ahmedx]

write this and show the result

[obriaincian]

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

[Ahmedx]

and try this

[obriaincian]

@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."

[Ahmedx]

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

[Ahmedx]

pls try again

[Ahmedx]

show this step

[obriaincian]

@Ahmedx 

[Ahmedx]

show it

[Ahmedx]

show next steps

[obriaincian]

@Ahmedx 

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

[Ahmedx]

pls try this

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

[obriaincian]

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

[Ahmedx]

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

[Ahmedx]

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

[Ahmedx]

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?

[obriaincian]

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

[MFelix]

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?