Forum Discussion
Use DAX or Power Query to create a calculated column
I have a table called Address as shown below
I want to use power query or dax to create calculated column to achieve AddressStatus (current and previous address) as shown below
How can i achieve this in power query
ok so if there is no rule, what is the rule you want to select the data, just the first one in sequential order?
I would add an index in the transformation layer (power query), under add column / index column
and then create a dax measure
addressstatus =
VAR person =
SELECTEDVALUE ( 'table'[fullname] )
VAR currindex =
SELECTEDVALUE ( 'table'[idcolumn] )
VAR minid =
CALCULATE ( min ( 'table'[idcolumn] ), 'table'[fullname] = person )
VAR status =
IF ( currenindex = minid, "Current Address", "PreviousAddress" )
RETURN
status
6 Replies
Hi Anonymous
what is the rule to define the current address? any indexes or dates or smth else?
az38 - there is no rule that define current address. The table was parsed on JSON, no index, no date.
ok so if there is no rule, what is the rule you want to select the data, just the first one in sequential order?
I would add an index in the transformation layer (power query), under add column / index column
and then create a dax measure
addressstatus =
VAR person =
SELECTEDVALUE ( 'table'[fullname] )
VAR currindex =
SELECTEDVALUE ( 'table'[idcolumn] )
VAR minid =
CALCULATE ( min ( 'table'[idcolumn] ), 'table'[fullname] = person )
VAR status =
IF ( currenindex = minid, "Current Address", "PreviousAddress" )
RETURN
status
how do you know which is which? is there a date column, or is it the first in line?
if first inline you can create a index column to extract the data out, if you can provide the data in text format, not a picture that would be good and i can demonstrate. easier with data.
vanessafvg - There is no date with the table. All the records was parsed on JSON and expanded address resulted to the table above. When i look at the json structure, the first is the current address.
Hi Anonymous,
Did vanessafvg 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.
If that also doesn't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
