Help needed: Transforming this data into useful tabular format for analysis

[k3nz3n]

Hi, I have this set of data that is not very tabular friendly.

I have been playing around with power query to see if it has the answer to it but to no avail. I need to get the SKU code (e.g 11190014 to be a column for store A, and 11200001 to Store B and so on - in order for it to be really tabular). Been trying around with all the functions (with little knowledge of power query formula's) but I can't seem to nail it.

Can anybody point me in the right direction?

Name	Quantity	Purchase Amount
11190014
Store A	3	35.66
Store A	3	35.66
Store A	2	23.78
Store A	1	11.89
Store A	2	23.77
Store A	15	178.3
11200001
Store B	1	24.43
Store B	5	122.17
Store B	1	32.52
11200005
Store C	1	8.4
Store C	1	8.4
Store C	36	31.8
11210002
Store D	10	11.79

 

[Brian Smith]

Here you go. I created a column to bring over the value of the name column when quantity was null and return null when qty has a value. This brings in just the SKU code. Then I used the fill down function. Then removed the original SKU code rows. This could give you a problem if you had null values in the qty column that were not SKU rows. You could build a formula looking for the text "store" as well and accomplish the same thing.

 

[k3nz3n]

Here you go. I created a column to bring over the value of the name column when quantity was null and return null when qty has a value. This brings in just the SKU code. Then I used the fill down function. Then removed the original SKU code rows. This could give you a problem if you had null values in the qty column that were not SKU rows. You could build a formula looking for the text "store" as well and accomplish the same thing.

SUPER helpful and exactly what I would need. However I am a noob at power query and I see the logic, however lack the know how to get there.

If I were to follow your steps, I would:
1) Create a duplicate column of "Name" column

2) When you say "when quantity was null and return null when qty has a value" I have no idea what to click or type in this step

3) Fill down the SKU code to replace all the nulls

Can you please shed some light on the step by step for "Step 2" please? Thanks!

 

[Pecoflyer]

Please don't quote entire posts unnecessarily. Thx

 

[Brian Smith]

In a bit more detail:

The new column is a custom column.

Add Column -> Add Custom -> if [Quantity]=null then [Name] else null

What we are doing here is like an if function in excel. We are telling Power Query that in this custom column, if the value of the quantity column is null then return the value of the name column. If not, return the value null. Since the only values where the quantity column is null is when the SKU number is in the row, the result of the column should be the SKU numbers and nulls.

Highlight the resulting column -> right click -> fill -> fill down

This will replace the null values with the value that is above it. It is like double clicking in the bottom right corner of a cell in excel to fill down values in a column.

If you go into the query in my sheet you can walk through each of the steps and it will show them in order.

 

[k3nz3n]

Got it! Clear, concise and problem solved. Thanks a bunch!

 

[Brian Smith]

My pleasure!