How to change "category rows" into columns?

[jhoffstein]

Hello,

I have a spreadsheet where the data is listed by "category rows", like the example below.  

To use this data in Power BI I need it to look like the table below, where the category is transformed into its own column.  

Is there a transformation I can apply that will automatically recognize these category rows and change them into a column, applying the right category label to each row?

This also needs to be dynamic, so if additional categories like "MEATS" or "BAKERY ITEMS" were added the transformation would automatically work for those as well.

Thank you for your help!

-Jenna

[ChrisMendoza]

@jhoffstein,

You can try the following:

Insert new column index starting at 0. In Applied Steps, I renamed to 'AddIndex'.

= Table.AddIndexColumn(#"Changed Type", "Index", 0, 1)

Review the documentation at https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/ as it helped to define the possible variables separately from the next step.

Here is the fnSwitch( ) I made to start you off. This is really so you don't have to write a bunch of 'or' in the next add column.

(input) =>
let
values = {
    {"FRUITS", "FRUITS"},
    {"VEGETABLES", "VEGETABLES"},
    {"BAKERY", "BAKERY"},
    {"CHEESE", "CHEESE"}
},
Result = List.First(List.Select(values, each _{0}=input)) {1}
in
Result

Add custom column 'Type'.

try if AddIndex[Column1] {[Index]} = fnSwitch([Column1]) then AddIndex[Column1] {[Index]} else null otherwise null

Filter 'Type' down

Filter one of the 'Year' columns null values out.

You should end up with:

[jhoffstein]

@ChrisMendoza I haven't had the chance to try this out yet, but would it work with additional categories beyond Fruits, Vegetables, Bakery and Cheese?  I'd like this to be as hands-off as possible, so I don't need to manually edit something every time the categories are changed.

Thanks,

Jenna

[ChrisMendoza]

@jhoffstein,

The accepted solution is the most hands off if the null value is constant/consistent in your data. 

[jthomson · Accepted solution]

Assuming that, say, 2017 is always going to have data in except on your rows, you could try making a new column in the query editor that pulls through the column with fruits, vegetables etc in if 2017 is blank and leaves it blank otherwise. IIRC there's then an option to fill down, which should populate the new column with the category headers as needed. You can then filter out anything in 2017 that's blank and you should be there

[jhoffstein]

@jthomson Worked perfectly - thank you!