Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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