Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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
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.
Proud to be a Super User!
@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
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
Proud to be a Super User!
User | Count |
---|---|
84 | |
72 | |
71 | |
68 | |
55 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |