I have simplified my challenge. I am trying to unpivot two columns with three rows. The third row has a null value in both columns and when I unpivot, I lose that row which I do not want to. Have understood there was an issue at the source and this will be corrected going forward. How can I solve this issue?
I have attached an example file here. The last two columns need to be unpivoted and I need all rows.
Unpivoting_Example.xlsx (9.8 KB)
@Carlton ,
Simple trick once you see it. Just replace your nulls with 0, unpivot and then replace the 0s with null.
Solution file attached.
Thank you, Brian. I now have six (double) the number of rows. I just need three rows. One with Carlton in the first column and Rita in the Value Column. The second row with Urmila and Adelaide and the third row with Mike and empty in the Value column.
If all the value column names start with Fam, you can use this approach.
Unpivoting Columns.pbix (27.9 KB)
Edited - Reduced number of steps
@Carlton I am a little confused what you are trying to achieve with your data. Can you please clarify further?
Looking at your sample data, it would appear that FamOm only has valid values when Country = Oman, and FamKw only has valid values when Country = Kuwait.
If this is the case, there is no need to unpivot, you can simply merge the columns, and the row integrity will be maintained. You can then just remove the Country column if it is not needed.
If this is not the case, can you please expand on the sample data, to fully reflect the behavior you are having to wrangle.
Thank you @JasonCockington , This is exactly what I wanted. I was overthinking this with unpivoting when a simple column merge was the way to go. The row integrity was maintained.
Thank you @Rajesh. I was overthinking this challenge by trying to unpivot. I just had to merge both the columns and still retain my row integrity.
For the sake of the original question, I wonder if moving the last (blank) column to the other position can help?
