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.
Hello Guys i have a dataset that looks like this
Task | Date | State |
A | 1/1/2022 | Enter |
A | 1/2/2022 | Break |
A | 1/3/2022 | Continue |
B | 1/4/2022 | Enter |
B | 1/5/2022 | Fall out |
B | 1/6/2022 | Carrier Step |
C | 1/7/2022 | Enter |
C | 1/8/2022 | Go Live |
C | 1/9/2022 | Retrace |
I am trying to create another column that will tell me the previous state based on the date for each state per task.
output would look like this
Task | Date | State | Previous State |
A | 1/1/2022 | Enter | |
A | 1/2/2022 | Break | Enter |
A | 1/3/2022 | Continue | Break |
B | 1/4/2022 | Enter | |
B | 1/5/2022 | Fall out | Enter |
B | 1/6/2022 | Carrier Step | Fall out |
C | 1/7/2022 | Enter | |
C | 1/8/2022 | Go Live | Enter |
C | 1/9/2022 | Retrace | Go Live |
I need to do this in power query because there are some other transformations i need to with that column before loading it.
Solved! Go to Solution.
Solution file uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuSf4jBWInubgMG_Q?e=K1J6Dq
Below is M Code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Date", type date}, {"State", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Task"}, {{"Temp", each _, type table [Task=nullable text, Date=nullable date, State=nullable text]}}),
//Function Start
fxProcessTable = (InputTable)=>
let
#"Added Index" = Table.AddIndexColumn(InputTable, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Previous State", each try if [State]="Enter" then "null" else #"Added Index"[State]{[Index]-1} otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns",
//Function End
#"Custom" = Table.AddColumn(#"Grouped Rows", "RunFunction", each fxProcessTable([Temp])),
#"Expanded RunFunction" = Table.ExpandTableColumn(Custom, "RunFunction", {"Date", "State", "Previous State"}, {"Date", "State", "Previous State"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded RunFunction",{"Temp"})
in
#"Removed Columns"
Solution file uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuSf4jBWInubgMG_Q?e=K1J6Dq
Below is M Code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Date", type date}, {"State", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Task"}, {{"Temp", each _, type table [Task=nullable text, Date=nullable date, State=nullable text]}}),
//Function Start
fxProcessTable = (InputTable)=>
let
#"Added Index" = Table.AddIndexColumn(InputTable, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Previous State", each try if [State]="Enter" then "null" else #"Added Index"[State]{[Index]-1} otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns",
//Function End
#"Custom" = Table.AddColumn(#"Grouped Rows", "RunFunction", each fxProcessTable([Temp])),
#"Expanded RunFunction" = Table.ExpandTableColumn(Custom, "RunFunction", {"Date", "State", "Previous State"}, {"Date", "State", "Previous State"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded RunFunction",{"Temp"})
in
#"Removed Columns"
I figured out a way around that is similar to this but was not working but your solution has pointed me to what was missing.
Thank you so much, this helped alot
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.