Forum Discussion
Calculating Dates across Rows
Hi,
Dealing with somewhat of an odd problem. The example below, the data gets pulled automatically and the dates for a single report show up in different rows.
I know how to calculate # of days when the dates are in the same row, but not clear how to do it across rows. Any suggestions?
Including an image below - trying to figure out how to calculate # of days between the two circled dates.
Hi amirzoyan,
I created a simple for test.
Please try.
Transform data.
Select all needed columns.
Unpivot Columns.
Select [Attribute] columns.
Pivot Column.
Close and Apply.
Attach the PBIX file for reference. Hope it helps.
Best Regards,
Community Support Team_GaoIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
6 Replies
- Hi, Here is a quick hack which will help you bring all the dates in 1 row and then you can easily find Date Difference. Step 1: Unpivot all the rows = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value") Step 2: Pivot the rows back. It will give you all the dates in single row. = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value") Hope it helps Prateek Raina
thanks for that!
but just to confirm - the steps you listed, how and where do I accomplish this?
This documentation is pretty good:
https://docs.microsoft.com/en-us/power-query/unpivot-column
- In Query Editor (Power Query)
- Please upvote my answer if it helped resolve your issue 🙂 Prateek Raina
Hi amirzoyan,
I created a simple for test.
Please try.
Transform data.
Select all needed columns.
Unpivot Columns.
Select [Attribute] columns.
Pivot Column.
Close and Apply.
Attach the PBIX file for reference. Hope it helps.
Best Regards,
Community Support Team_GaoIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
