An recent AKA attendee described a puzzling issue: queries driven by dates in worksheet cells kept showing old values after a refresh. Only clicking Refresh Preview, or saving, closing and reopening the file, seemed to fix it. 🤔
@excelguru.ca's diagnosis? The data probably wasn't wrong at all. 🧵
![Master Your Data in Excel & Power BI
Pivoting Stacked Data Recipe
- Add a columns for transaction and line IDs
- Add Column -> Index Column -> From 0
- Select [Index] -> Add Column -> Standard -> Integer-Divide*
- Select [Index] -> Transform -> Standard -> Modulo*
- Pivot the data
Select [Index] -> Transform -> Pivot Column
- Set the Values field to your data column
- Open the Advanced option and “Don’t Aggregate”
- Right click the [Integer-Division] column -> Remove
- Rename the newly created columns
- Set your data types
*Modulo and Integer Division always target the number representing the 1st row of the 2nd transaction
Recipe 10.120.2112
For more data patterns, check out our courses at skillwave.training](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:ihdq52obivvf6sq3lnalnwrv/bafkreif5b4ytc5womlmee233vx7t4vdrpoads3e7arelwaxd6bsvrk5j6e)
![Untangling ISO Weeks Into a Real Calendar
Ken Puls built a student a quick custom Power Query function that unpacks any ISO week into real calendar dates. From there, he expanded the calendar to add other key periods for dimensional reporting.
Screenshot of the Power Query editor with a table with 2 columns: Week ID (containing the ISO week codes) and Date (containing Lists). The formula bar contains the following function:
=Table.AddColumn(#”Removed Columns”, “Date”, each fnISOWeekIDToDates ([Week ID]))
Below the table is one of the expanded Lists containing the calendar dates for the corresponding ISO week code.
(Skillwave Training logo)](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:ihdq52obivvf6sq3lnalnwrv/bafkreiduatpnh4qcribdox3j23omvhouaiz6ckebkxw574l3pq26ednu7a)
