Skip to main content
cancel
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.

Reply
obriaincian
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     
1JohnCoomey3252365
2

Mary

Lindsay

4653696
3TinaBlackwell5445838
4MichaelButton3783647
5JudyMitchell2566366
6MiaDunne4699524

 

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

23 REPLIES 23
Ahmedx
Super User
Super User

pls try this code

Screenshot_1.png

 

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"

 

@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

Screenshot_5.png

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

 

obriaincian_0-1699972526344.png

 

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

Screenshot_1.png

pls try again

 

show this step

Screenshot_1.png

@Ahmedx 

obriaincian_1-1699964435099.png

 

 

show it

Screenshot_2.png

show next steps

@Ahmedx 

 

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

 

This column shows the sum

obriaincian_0-1699965016835.png

 

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

 

obriaincian_1-1699965143381.png

 

 

 

 

pls try this

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

Screenshot_4.png

 

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

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


Regards

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

Announcements
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

PBI_APRIL_CAROUSEL1

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.