Separating columns in Power Query

[Antoinette123]

Hi! There is a pivot table. How to separate the column "Plan-Fact" (that has options -kinda filters 'Plan', 'Fact' inside of it) into 2 separate columns named "Plan" and "Fact"?

 

[Anonymous]

Hi @Antoinette123 ，

Is your problem solved? If not, can you provide me with some of what it looks like in your Power Query?

It would be nice to have a more detailed description of the problem, thank you.

When you provide screenshots, please protect your privacy.

Best Regards,

Stephen Tao

[ahadkarimi]

Hey @Antoinette123, try and let me know if there is any problem.

Load Data into Power Query.
Select "Plan-Fact" Column.
Transform → Split Column → By Delimiter (choose appropriate delimiter).
Rename Columns to "Plan" and "Fact".
Close & Load.

[Antoinette123]

I've tried that, it doesn't do what I need, unfortunately. Cause the whole Plan/Fact column has only 2 possible values: either "Plan" or "Fact", not 2 values to separate them by  delimiters

[ahadkarimi]

@Antoinette123 try this way and let me know if there is any issue:

Select "Plan-Fact" and any key columns.
Go to Transform → Pivot Column.
Pivot "Plan-Fact", choose your values column, set to "Don’t Aggregate".
Hit Close & Load.

[Antoinette123]

No, also not that. I got something strange 🙂

[ahadkarimi]

@Antoinette123 Could you explain it in more detail, or provide a better example, or if possible, provide and share a part of the sample file so that I can help better?

[Antoinette123]

Where can I send you the file/mail?

[ahadkarimi]

you can use one drive or send it to [email protected]

[Antoinette123]

Thank you! I sent the file. In the file the "plan-fact" column is kinda a filter: you'll get a column or both columns depending on what you choose.Instead, I need 2 separate columns, so I can do some calculations, like fact devideved by plan etc.

[ahadkarimi]

@Antoinette123, do you load this Excel file in Power BI?
I see that it creates 2 different columns for plan and fact.

[Antoinette123]

No, it was an ordinary table, then I used power query to create a pivot table. I was told to create a table similar to what I did, but with 2 separate columns.

[Antoinette123]

I selected "Plan-Fact" and Month columns. Went to Transform → Pivot Column. Pivot "Plan-Fact", set to "Don’t Aggregate". And in the final table there was only 1 column, not 2 separate "plan" and "fact" columns

[ahadkarimi]

@Antoinette123c, can you send another photo like the one below and explain a bit more clearly?