Forum Discussion
Solved
How to get previous state for each state per customer
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.
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"
3 Replies
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"Thank you so much, this helped alot
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.
