Forum Discussion
Unpivot Date Column - First Post
Hi Power BI Community!
This is my first post to this forum, so please let me know if I need to make any adjustments.
Essentially what I'm trying to do is take each unique ATM ID and show status by date over the last 30 days, with each date/status in their own columns. For example:
| ATM ID | Date 30 Days Ago | Status 30 Days Ago | Date 29 Days Ago | Status 29 Days Ago | Date 28 Days Ago | Status 28 Day Ago | etc. | etc. | etc. |
| 1 | 1/21/23 | Online | 1/22/23 | Erroring | 1/23/23 | Online | |||
| 2 | 1/21/23 | Erroring | 1/22/23 | Erroring | 1/23/23 | Online |
Here is a screenshot of what I've done so far. This Database table is a snapshot by day of status for each ATM. I've filtered the snapshot column to only be within the last 30 days. Thoughts on what I can do here?
let
Source = Sql.Database("pmgsql.database.windows.net", "btmdata"),
dbo_MachineStatusSnapshots = Source{[Schema="dbo",Item="MachineStatusSnapshots"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_MachineStatusSnapshots, each Date.IsInPreviousNDays([Snapshot], 30)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"AtmId", "Status", "Snapshot"}),
#"Inserted Date" = Table.AddColumn(#"Removed Other Columns", "Date", each Date.From([Snapshot]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Date",{"Date"}),NewStep=Table.FromRecords(Table.Group(PreviousStepName,"Atmid",{"n",each let a=Date.From(DateTime.LocalNow()),b=Table.TransformColumns(_,{"Snapshot",Date.From}) in Record.FromTable(Table.FillDown(#table({"Name","Value"],{{"Atmid",a[Atmid]{0}}}& List.TransformMany({0..29},each {"Date","Status"},(x,y)=>{y&" "&Text.From(30-x)&" Days Ago",if y="Date" then Date.AddDays(a,x-30) else b{[Snapshot=Date.AddDays(a,x-30)]}?[Status]?})),{"Value"}))})[n])
in
NewStep
7 Replies
NewStep=Table.FromRecords(Table.Group(PreviousStepName,"Atmid",{"n",each let a=Date.From(DateTime.LocalNow()),b=Table.TransformColumns(_,{"Snapshot",Date.From}) in Record.FromTable(Table.FillDown(#table({"Name","Value"],{{"Atmid",a[Atmid]{0}}}& List.TransformMany({0..29},each {"Date","Status"},(x,y)=>{y&" "&Text.From(30-x)&" Days Ago",if y="Date" then Date.AddDays(a,x-30) else b{[Snapshot=Date.AddDays(a,x-30)]}?[Status]?})),{"Value"}))})[n])
I'm excited to try this! Thank you for your response. How can I go about adding this? Here is my current advanced editor in power query:
let
Source = Sql.Database("pmgsql.database.windows.net", "btmdata"),
dbo_MachineStatusSnapshots = Source{[Schema="dbo",Item="MachineStatusSnapshots"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_MachineStatusSnapshots, each Date.IsInPreviousNDays([Snapshot], 30)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"AtmId", "Status", "Snapshot"}),
#"Inserted Date" = Table.AddColumn(#"Removed Other Columns", "Date", each Date.From([Snapshot]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Date",{"Date"})
in
#"Removed Columns"let
Source = Sql.Database("pmgsql.database.windows.net", "btmdata"),
dbo_MachineStatusSnapshots = Source{[Schema="dbo",Item="MachineStatusSnapshots"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_MachineStatusSnapshots, each Date.IsInPreviousNDays([Snapshot], 30)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"AtmId", "Status", "Snapshot"}),
#"Inserted Date" = Table.AddColumn(#"Removed Other Columns", "Date", each Date.From([Snapshot]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Date",{"Date"}),NewStep=Table.FromRecords(Table.Group(PreviousStepName,"Atmid",{"n",each let a=Date.From(DateTime.LocalNow()),b=Table.TransformColumns(_,{"Snapshot",Date.From}) in Record.FromTable(Table.FillDown(#table({"Name","Value"],{{"Atmid",a[Atmid]{0}}}& List.TransformMany({0..29},each {"Date","Status"},(x,y)=>{y&" "&Text.From(30-x)&" Days Ago",if y="Date" then Date.AddDays(a,x-30) else b{[Snapshot=Date.AddDays(a,x-30)]}?[Status]?})),{"Value"}))})[n])
in
NewStep
Hi,
it is not clear to me which is your table and which is your desired result
Thank you for your reply! I, unfortunately, haven't found a solution yet.
To clarity, the 1st table in the post is my desired result. The screenshot below it is my current table / Power Query screen.
Hi David - I corrected the error and was able to run the step you identified. It did make the ATM Id error out, but I was able to replicate the solution using the matrix visual. I appreciate your willingness to solve this problem with me, and would greatly appreciate your help/advice in the future.
