DAX Formulas transform Power Query M Formulas

[Anonymous]

Hi There,

I have added a column with DAX formulas on front side in Power BI desktop like this:

Last Choice= 

VAR _lastIndex = CALCULATE( 
    MAX( 'answers'[_id] ), 
    ALLEXCEPT( 'answers', 'answers'[UserID], 'answers'[QuestionDescription])
)
RETURN CALCULATE( 
    SELECTEDVALUE( 'answers'[AnswerText] ), 
    ALL( 'answers' ),
    'answers'[_id] = _lastIndex 
)

But I want to create this column in power query side. How can we do that?

Best,

[Anonymous]

Hi @StefanoGrimaldi ,

I don't have an ID column to sort, I have ID column like this:

ID

5f563e7c2a0b587e1db5ebd7

5f576bc72a0b587e1dbcaf0d

5f563e7c2a0b587e1db5ebd8

5f576bc72a0b587e1dbcaf0c

That is MongoDBs ObjectID.

And I need Last AnswerText for user and questiondescription. so I need a column as this:

UserID QuestionDesc. LastAnswer

user1  question1 answer2

user1 question2 answer1

user2 question1 answer3

[StefanoGrimaldi]

any other field you can get  or extract from the source? since you would need a reference to us as registration order, or to make a index column to be able to get the last entry per user. for example: if the answer are called to your table in registration order you can simply add an index column starting by 0 and them follow the above steps, if not the case them you will need a column source that give your this cronological order data. 

[StefanoGrimaldi]

yes can be done, for what Im seeing (Please correct me if mistaken)  each new response generate a new ID lets says A1 +1, A2+1 and so on, so the last number of your column gives you what you need to sort the responses in order, for this go to the home tab in power query interface and use the split column option, sinces your ID always beggins with a A followed by a number use the option of spliting by number of characters from right to left by 1 

if you need the original ID column as its , duplicate it before splitting. 

them change the column left with number to number type, and sort it in order from lower to biggest to get it in order, them use the Index fuction to add a index to allocate a order finally. 

with thois you would have your data in cronological order, now your example shows me you want them to add a column with the last user response,

them you can use the group by function to make this happend: 

let me know if this works for your need

[StefanoGrimaldi]

as you need it as a column you can make this a second table and disable the loading of it, and in a new table without the group by step you can use a merge fucntion to call this as a new column, like this:

[Anonymous]

Hi @StefanoGrimaldi 

Thank you for your response but I need actually a new column. If you say me how can I add a new column in Power Query ediyor, that's enough for me. I try to explain what I need below:

I have a table like this:

I need a Last Choice column Like this table:

I dont have date for this. We can sort this data only for id. I dan do it in DAX but I need it in power query.

I hope It can be done.

Best,

[StefanoGrimaldi]

You can sort by the date in power query then add the index so th index 0 would be always index 0 then filter the index column for only value 0, 

Power Query M code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tci5DQAxDAOwXVwHUCw/ywQZ4fZvT0UKs+Q59rUtazS4ue0ujWscPoYagmNCE4gxqUnkmNIU6s39AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"data", type text}, {"date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"date", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 0))
in
#"Filtered Rows"

initial table: 

Result: 

 If this have solved your problem Please mark it as a solution, so others can find this solution quickly, If you found this post helpful, please give Kudos.