Adding a new column referring to a column and value of next row.

[kosukey05]

Hello Power BI experts,

I'm new to power bi and trying to find a way to achive my requirment but couldnot find how to solve.

I want to visualize the movement of users.

I have this table.

user	location
aaa	tokyo
aaa	osaka
aaa	nagoya
aaa	hakone
aaa	kyoto
bbb	sapporo
bbb	nara
ccc	tokyo
ccc	hakone
ccc	osaka
ddd	osaka
ddd	tokyo

How I want to transform this table is 

user	From	To
aaa	tokyo	osaka
aaa	osaka	nagoya
aaa	nagoya	hakone
aaa	hakone	kyoto
bbb	sapporo	nara
ccc	tokyo	hakone
ccc	hakone	osaka
ddd	osaka	tokyo

I need to add the next value of location into "to" as a new column, but user cannot be mixed. 

It would be great if anyone can suggest how I can achive this.

Thank you!

[Ashish_Mathur]

Hi,

You may refer to my solution at this link.

Hope this helps.

[Ashish_Mathur]

Hi,

I think i have been able to solve the problem.  Please allow me time until the weekend to share my solution with you.

[PattemManohar]

@kosukey05 I've tried this to solve your scenario...

Added a new index column and "New Column" as below

To = LOOKUPVALUE(UserLocations[location],UserLocations[user],UserLocations[user],UserLocations[Index],UserLocations[Index]+1)

Looks something like this.... (Note - I've duplicated location field as From Field as well)

Then add a "New Table" which will be your expected output as below..

UserLocationsOutput = CALCULATETABLE(
                                 SELECTCOLUMNS(FILTER(ALL(UserLocations),NOT ISBLANK(UserLocations[To])),"User",UserLocations[user],"From",UserLocations[From],"To",UserLocations[To])
                                 )

[affan · Accepted solution]

Hi @kosukey05,

Considering that you have mentioned yourself a biggener level user of Power BI. I have used the information from @Greg_Deckler's blogpost to create a pbix for you.

You can create an Index column in your table by going to 'Edit Queries' and click on Index column in the Add Column tab on the ribbon.

Once you have added an index you can use the below to add a calculated column as below. I have also attached a pbix file for this example which you can review.

To_Location = VAR fromLoc = MINX(FILTER(Table2,Table2[user]=EARLIER(Table2[user])&& Table2[Index]>EARLIER(Table2[Index],1)),Table2[Index])
    Return 
    CALCULATE(FIRSTNONBLANK(Table2[From_location],1),FILTER(Table2,Table2[Index]=fromLoc))

You can review the pbix file 

https://www.dropbox.com/s/4brimatrmndxy0h/FROM-TO%20Location.zip?dl=0

Please accept as solution if this helped.

Regards,

Affan

[Greg_Deckler]

Add an Index column in Power Query and then follow this article. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...