Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm trying to achieve something in power query and I'm struggling. I have done this with DAX but with power query I am clearly missing something.
I have a status evolution table such as the one in the imagem below, where each line has a unique ID, a group ID, the old and new status and the transition date. What I need is to create a new column where the first transition to status4 for each group is the value in this new column. I tried to show an example in the image below.
I attempted to do this with the following logic but I was unsuccessful:
= Table.AddColumn(
#"Colunas Removidas1", "newColumn",
each let
groupId = [groupId],
minHistId = List.Min(Table.SelectRows(#"Colunas Removidas1",
each
[groupId] = groupId
and [transitionDate] = Date.From(DateTime.Date([transitionDate]))) [uniqueId]),
transitionDate =
if [newStatus] = "STATUS4"
then
Date.From(DateTime.Date(List.Min(Table.SelectRows(#"Colunas Removidas1",
each [groupId] = groupId
and [uniqueId] = minHistId
and [transitionDate] = Date.From(DateTime.Date(List.Min(Table.SelectRows(#"Colunas Removidas1", each [groupId] = groupId and [newStatus] = "STATUS4")[transitionDate])))))[transitionDate]))
else null in transitionDate)
This correctly selects the rows where the first transition is present since all other values are null, but the rows where the calculated value was supposed to be have the following error: "Expression.Error: Cannot convert a value of type Table to type List."
Thank you in advance.
=let a=Table.Group(#"Colunas Removidas1","GroupId",{"n",each Table.Skip(_,each [newStatus]<>"STATUS4"){0}?[transitionDate]?}) in Table.AddColumn(#"Colunas Removidas1","newColumn",each a{[GroupId=[GroupId]]}?[n]?)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.