Unpivot Date Column - First Post

[daez12]

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?

[daez12]

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.

[wdx223_Daniel]

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])

[daez12]

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"

[wdx223_Daniel · Accepted solution]

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

[daez12]

Thank you for the follow-up Daniel!

It may be a novice error on my end, but here is the error I'm seeing:

It looks like the bracket in New Step is highlighted upon clicking "Show Error." Not sure if this is helpful or not.

Thoughts?

[serpiva64]

Hi,

it is not clear to me which is your table and which is your desired result

[daez12]

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.