Forum Discussion
Unpivot without keeping zeros
Hello,
I have a table with articles and the quantities sold are in different column for each month of the year.
When i unpivot them I obtain a quantity of rows containing only zeros and wanted to know if there is a way to filter them during the process, since I tried to filter them afterwards and ended up with 30 minutes wait for it to load into the model.
The table before
Here is what it looks like afterwards with the code
= Table.Unpivot(#"Colonnes permutées", {"Qte Janvier", "Qte Février", "Qte Mars", "Qte Avril", "Qte Mai", "Qte Juin", "Qte Juillet", "Qte Août", "Qte Septembre", "Qte Octobre", "Qte Novembre", "Qte Décembre"}, "Attribut", "Valeur")
Thanks in advance for the help 👍
Sebastien
Please consider this solution and click the thumbs up icon for me just trying to help you and click ACCEPT SOLUTION if it fixes your problem. Thank you !
Option 1)
Go back to the data sorce (eg SQL view) and send the data will null instead of 0.
The unpivot will then run much faster.
Option 2)
Select columns Jan to Dec and replace 0 with null before doing the unpivot
2 Replies
I think if you replace 0 with null in all of the columns you intend to unpivot, then those values won't show up after unpivoting.
Please consider this solution and click the thumbs up icon for me just trying to help you and click ACCEPT SOLUTION if it fixes your problem. Thank you !
Option 1)
Go back to the data sorce (eg SQL view) and send the data will null instead of 0.
The unpivot will then run much faster.
Option 2)
Select columns Jan to Dec and replace 0 with null before doing the unpivot
