Help with setting up a formula for sorting and displaying

[Anonymous]

I want some help with a formula in Power Bi , was wondering if you able to assist .

Here is an example of the data 

How do i sort on purchase ,so that all the pages visisted by the IP address  are displayed when sorting on packages 

[ToddChitt]

Can you give us a sample of what the output should look like?

Maybe try a simple TABLE visual, and add Purchase, IP address, and a COUNT of some column. That will give you the number of times any one Address was visited for each Purchase.

What are the blank cells in the Purchase column? Should they be FILLED DOWN in Power Query?

[Anonymous]

@ToddChitt 

The blank cells in the purchase column explains the behavior and steps the IP addresses does before purchasing, this information is important as I am trying to see their  behavior before and after 

[Anonymous]

do i use count on column ?

[ToddChitt]

Can you explain the data? Is it that somone from IP address 119 hit three pages on 10-Oct and they are, in order: Welcome, Product, and Sale, and they ended up purchasing Package S? Then on 11- Oct someone from IP address 117 hit pages Welcome, Details, Product, Home, Sales, and purchased Premium S.

Seems like to need to first sort the data by IP Address then Date (I assume you have a Time componennt in the Date column?) and do a FILL UP in Power Query. That way, that first row, 10-Oct, 119, Welcome, has a value of Package S under Purchase.

[Anonymous]

yes the way you explained the data is correct

The date column illustrates date and time but i changed it to date only

Should I have the time componement also before doing your steps.

I havent used Fill up before so a bit unsure

[ToddChitt]

Time portion is important because, we assume, that the LAST thing anyone does is the PURCHASE step. So if you sort by IP Address then Date Time, that Sales/Purchase row will be at the bottom, as you have it displayed.

In Power Query, on the Transform menu, look for "Fill". Highlight the column first.

[Anonymous]

understood


In Power Query, on the Transform menu, look for "Fill". Highlight the column first.

Which column would this be, the date and time column  ?

[ToddChitt · Accepted solution]

SORT by IP Address and Date/Time first. Then highlight the Purchase column and Fill Up.

Remember, you can always delete an applied step in Power Query so no harm in experimenting.