fill

3 Topics

Fill with same value based on index column or minimize table to one column
Hi everyone, I am trying to solve the following problem where I have a table similar to the following one (in Power Query Editor): I would like to get two of the following result but I don't know how to get it: Thanks in advance.
Solved
Anonymous
3 years ago Place Report Server
588Views
0likes
1Comment
Filling up and down by catergory
Just looking for some help - below is an example of data i have - im looking at sales over 21 days periods. Each promo has a unique ID. When no sales are made in store I dont get a price. Im looking to fill in the blanks from the day after from each promo ID. There is potentially a chance there is no sales from the last days of the promo, in these stances id like the blanks to be filled with the pervious days value. The table below has blanks in prices, and ive created a column called wanted outcome to show what im trying to achieve. Any help with be greatly appreiceted as the simple fill options are no good for me here. PROMO ID RETAILER STORE FORMAT BRANCH NUMBER STORE NAME RETAILER_PRODUCT_CODE Client PRODUCT DESCRIPTION DATE Price Promo Day WANTED OUTCOME 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 21/04/2022 1 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 22/04/2022 £1.50 2 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 23/04/2022 £1.50 3 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 24/04/2022 4 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 25/04/2022 £1.50 5 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 26/04/2022 £1.50 6 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 27/04/2022 £1.50 7 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 28/04/2022 £1.50 8 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 29/04/2022 £1.50 9 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 30/04/2022 £1.50 10 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 01/05/2022 11 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 02/05/2022 12 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 03/05/2022 13 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 04/05/2022 £1.50 14 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 05/05/2022 £1.50 15 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 06/05/2022 16 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 07/05/2022 £1.50 17 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 08/05/2022 18 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 09/05/2022 £1.50 19 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 10/05/2022 £1.50 20 £1.50 1 Retailer 1 Format 1 Store 1 Store Name 1 1001 Client 1 Product 1 11/05/2022 £1.50 21 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 21/04/2022 1 £2.00 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 22/04/2022 £2.00 2 £2.00 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 23/04/2022 £1.50 3 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 24/04/2022 £1.50 4 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 25/04/2022 £1.50 5 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 26/04/2022 £1.50 6 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 27/04/2022 £1.50 7 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 28/04/2022 £1.50 8 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 29/04/2022 £1.50 9 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 30/04/2022 £1.50 10 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 01/05/2022 £1.50 11 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 02/05/2022 £1.50 12 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 03/05/2022 £1.50 13 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 04/05/2022 £1.50 14 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 05/05/2022 £1.50 15 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 06/05/2022 £1.50 16 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 07/05/2022 £1.50 17 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 08/05/2022 £1.50 18 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 09/05/2022 £1.50 19 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 10/05/2022 £1.50 20 £1.50 2 Retailer 1 Format 1 Store 2 Store Name 2 1001 Client 1 Product 1 11/05/2022 £1.50 21 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 21/04/2022 £1.50 1 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 22/04/2022 £1.50 2 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 23/04/2022 3 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 24/04/2022 4 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 25/04/2022 £1.50 5 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 26/04/2022 £1.50 6 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 27/04/2022 £1.50 7 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 28/04/2022 8 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 29/04/2022 £1.50 9 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 30/04/2022 10 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 01/05/2022 11 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 02/05/2022 £1.50 12 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 03/05/2022 £1.50 13 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 04/05/2022 £1.50 14 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 05/05/2022 £1.50 15 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 06/05/2022 16 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 07/05/2022 17 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 08/05/2022 £1.50 18 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 09/05/2022 £1.50 19 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 10/05/2022 20 £1.50 3 Retailer 1 Format 1 Store 3 Store Name 3 1001 Client 1 Product 1 11/05/2022 21 £1.50
Solved
michaeldonnelly
4 years ago Place DAX Commands and Tips
972Views
0likes
5Comments
Fill Blanks with latest if based on sequence
Hello there, I'm having some trouble on solving this one. To give a breif example, we have a sequence from 1 - 5 each with a status and date. Sometimes the user skips this sequence and now I'd like to fill the gap with the latest date available. Example ID Status StatusID LatestDate 025755 Intial Lead 1 Jan 1 2021 025755 Regular Lead 2 Jan 5 2021 025755 Hot Lead 3 To be filled 025755 Signature Pending 4 To be filled 025755 Success 5 Jan 25 2021 Example2 ID Status StatusID LatestDate 025755 Intial Lead 1 Jan 1 2021 025755 Regular Lead 2 To be filled 025755 Hot Lead 3 14 Jan 2021 025755 Signature Pending 4 025755 Success 5 Now as you can see we have 2 statuses in example 1 that were skipped and thus have no dates. My objective is to fill these blank spaces for each group(ID) with the latest date available based on the max status/statusID. In example 2, 1 status was skipped and the latest current Id is 3, so we only need to fill the one in between. By default, status(ID) 1 always has a date. Thanks!
Solved
Anonymous
4 years ago Place Desktop
1.3KViews
0likes
2Comments