Log In
Register
Home
Active Member
June 5, 2022
Offline
Hi, I have an excel sheet with nested headings (see attached), and in order to create pivot table I need to create a column for "country: and another column f for "practice group". I tried different features in Power Pivot and nothing seemed working. I would appreciate if anyone has any solution. Thank you so much in advance.
Moderators
January 31, 2022
Offline
Please see the attached file. It adds two columns to the data set. The first checks if there are two consecutive nulls in Column2. If so, then use Column1 (i.e. the country) else null. The next steps checks if both Column2 and the column we just created have nulls. If so, then use Column1 (i.e. the group) else null. Now, fill down and clean-up.
Active Member
June 5, 2022
Offline
Riny, thank you very much. Basically I will need to add conditional columns using the formula to achieve this.
Active Member
June 5, 2022
Offline
Riny, I am not good at writing the PQ formula,, and can I use Conditional Column in PQ? if so, what would be the answer to add each column, country and group? Do you mind taking a screenshot? thank you so much.
Moderators
January 31, 2022
Offline
Hi Peggy,
I don't think it's possible to do such a transformation by adding a Conditional column. The conditions you can select in such step work on the same row in the table. Something like if column1 contains this then return A else B.
In your case we need to look into the current AND the next row to determine if we are dealing with the country name in the current row. So, I don't know of any other way to do that then through adding a custom column with some special code. But, the code isn't all that much different from the standard if-then-else code that the "Add continual column" button would generate. Obviously, you need to add an index column first. I like to name that step "Idx".
The code via the conditional column (if you don't change the suggested name ´Custom´ for the new column) would be:
And the customised code is like this:
The underlined pieces of code, you don't have to worry about. PQ adds them for you.
The bolded code is the same in both and the red code is the customisation. You need to look into column2 on the next row as well. Idx refers to the previous step and [Column2]{[Index]+1} tells PQ to look in column2 on the row where the value in the Index column is 1 greater than the Index value on the current current row.
Obviously, you need to add an index column first. That's done by clicking in the user interface, but I like to rename the step "Idx" afterwards. Otherwise it will come up as #"Added Index" in the code for following steps.
For the Group column you can't use the Conditional button either as you have to add the AND piece (both column2 and custom must be null) into the code yourself.
PQ's greatest strength, in my opinion, is that you can achieve amazing transformations with just a few button clicks. But if you learn some basic M-code you can tweak the "standard code" and create magic.
1 Guest(s)
