Forum Discussion
PQ / Data Model - Turning non consecutive days into consecutive with the value from previous day
Better if you provide sample file instead of screenshot, but let try this way. I'd suggest Power Query
- yes, you need Date table, but don't generate in Excel sheet, you may find a lot of samples how to do such table by Power Query
- if second table is Source, reference on it, keep only Accounts column, remove duplicates, add custom column with =Date table, expand it on dates
- Merge (left join) this table with Source on Account and Date, expand Balance
- fix the table in memory by adding Index column and Group By on Accounts without aggregation
- in formula bar change each _ on each Table.FillDown(_,{"Balance"})
- expand column with grouped result
Now you may load query to data model and create PivotTable from it; or pivot query and return table to sheet, etc.
Better if you provide sample file instead of screenshot, but let try this way. I'd suggest Power Query
- yes, you need Date table, but don't generate in Excel sheet, you may find a lot of samples how to do such table by Power Query
- if second table is Source, reference on it, keep only Accounts column, remove duplicates, add custom column with =Date table, expand it on dates
- Merge (left join) this table with Source on Account and Date, expand Balance
- fix the table in memory by adding Index column and Group By on Accounts without aggregation
- in formula bar change each _ on each Table.FillDown(_,{"Balance"})
- expand column with grouped result
Now you may load query to data model and create PivotTable from it; or pivot query and return table to sheet, etc.
SergeiBaklan Thank you, that worked like a charm. And apologize for not incl. the actual sample file. Created a quick example as the data used in the model is sensitive.
JoakimHGjerde , you are welcome.
As a sample I mean only exactly the same as on your screenshot, but as file, nothing more. Anyway, good to know it works, thank you for the update.