Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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! Go to Solution.
Hi @michaeldonnelly ,
To get the expected output, you need group your table by PROMO ID category firstly, then do the fill up and down based on the grouped table. The complete M code is as follows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdjNasJAFAXgVwmui96ZiRqXMYlUqInESBciIjYLQSuIXfR5+iZ9sk6irWa4JXdzFuLM+POZCRyOs1p15nk2y7xp3Hnq5EkRTl+S3A4XRZYn3iTLZ2Fhp+M8TKNnL13Oxg8vp+EsefjYxn5VvIyKTZTF1Xp02JfvFzu4rXtxsojy6byYZqldjcOietf8vN+V9fPpePLi7acdv4ZpkcRetiyizBLrp1VHVVB52e4P5dmrJpPT+bi91MPF5XQuH0bp9nidKiL190PqFau8feyuY6165Pc0aW0nXvV2+/j+Ut0+wUj9QN4ou4p3DecavOs7W+zjyT53qX28O+DcAd4dcu4Q7wacG+DdEeeO4K4hzlUEh8nGVL8RU/icIu2a+Iwi45r4fLJ39G7ebyo+pazKwfiYooG7y/iIoiF7sfiMosC9WHw+0Yi9WHxAKeJgjQ8opVjYSSndCusmrFvhfwqc7hKObBa4mvotcFBXUuAQrs+5Pt6VtDiEK2lxCFfS4hCupMUhXEmLA7iyFgeAiQ1Jt8ohYM3CGg8bFsZnlqzUIWBRqUPAAxbGx5as2SHggIXxwSWrdwBYVu8QsKjemVbYNGHTCjfq3X2r8a7kkA7hGqfOGjzZcj6HICXNDuFKmh3ClTQ7hBs4tzbAk5JSB3Abpa7+H0hws/VUDmGKqhwCFlU5BCyqcghYVOUQcNv5HMIcuiY+nmQFDgGLChwAbhS4apfd7oYw3ZRq1Lb1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PROMO ID", Int64.Type}, {"RETAILER", type text}, {"STORE FORMAT", type text}, {"BRANCH NUMBER", type text}, {"STORE NAME", type text}, {"RETAILER_PRODUCT_CODE", Int64.Type}, {"Client", type text}, {"PRODUCT DESCRIPTION", type text}, {"DATE", type text}, {"Price", type text}, {"Promo Day", Int64.Type}, {"WANTED OUTCOME", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PROMO ID"}, {{"GroupedTable", each _, type table [PROMO ID=nullable number, RETAILER=nullable text, STORE FORMAT=nullable text, BRANCH NUMBER=nullable text, STORE NAME=nullable text, RETAILER_PRODUCT_CODE=nullable number, Client=nullable text, PRODUCT DESCRIPTION=nullable text, DATE=nullable text, Price=nullable text, Promo Day=nullable number, WANTED OUTCOME=nullable text]}}),
#"Filled Up" = Table.FillUp(#"Grouped Rows",{"GroupedTable"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"PROMO ID"}),
#"Expanded GroupedTable" = Table.ExpandTableColumn(#"Removed Columns", "GroupedTable", {"PROMO ID", "RETAILER", "STORE FORMAT", "BRANCH NUMBER", "STORE NAME", "RETAILER_PRODUCT_CODE", "Client", "PRODUCT DESCRIPTION", "DATE", "Price", "Promo Day", "WANTED OUTCOME"}, {"PROMO ID", "RETAILER", "STORE FORMAT", "BRANCH NUMBER", "STORE NAME", "RETAILER_PRODUCT_CODE", "Client", "PRODUCT DESCRIPTION", "DATE", "Price", "Promo Day", "WANTED OUTCOME"})
in
#"Expanded GroupedTable"
Also, attached the pbix file as reference.
If 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 let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi @michaeldonnelly ,
To get the expected output, you need group your table by PROMO ID category firstly, then do the fill up and down based on the grouped table. The complete M code is as follows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdjNasJAFAXgVwmui96ZiRqXMYlUqInESBciIjYLQSuIXfR5+iZ9sk6irWa4JXdzFuLM+POZCRyOs1p15nk2y7xp3Hnq5EkRTl+S3A4XRZYn3iTLZ2Fhp+M8TKNnL13Oxg8vp+EsefjYxn5VvIyKTZTF1Xp02JfvFzu4rXtxsojy6byYZqldjcOietf8vN+V9fPpePLi7acdv4ZpkcRetiyizBLrp1VHVVB52e4P5dmrJpPT+bi91MPF5XQuH0bp9nidKiL190PqFau8feyuY6165Pc0aW0nXvV2+/j+Ut0+wUj9QN4ou4p3DecavOs7W+zjyT53qX28O+DcAd4dcu4Q7wacG+DdEeeO4K4hzlUEh8nGVL8RU/icIu2a+Iwi45r4fLJ39G7ebyo+pazKwfiYooG7y/iIoiF7sfiMosC9WHw+0Yi9WHxAKeJgjQ8opVjYSSndCusmrFvhfwqc7hKObBa4mvotcFBXUuAQrs+5Pt6VtDiEK2lxCFfS4hCupMUhXEmLA7iyFgeAiQ1Jt8ohYM3CGg8bFsZnlqzUIWBRqUPAAxbGx5as2SHggIXxwSWrdwBYVu8QsKjemVbYNGHTCjfq3X2r8a7kkA7hGqfOGjzZcj6HICXNDuFKmh3ClTQ7hBs4tzbAk5JSB3Abpa7+H0hws/VUDmGKqhwCFlU5BCyqcghYVOUQcNv5HMIcuiY+nmQFDgGLChwAbhS4apfd7oYw3ZRq1Lb1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PROMO ID", Int64.Type}, {"RETAILER", type text}, {"STORE FORMAT", type text}, {"BRANCH NUMBER", type text}, {"STORE NAME", type text}, {"RETAILER_PRODUCT_CODE", Int64.Type}, {"Client", type text}, {"PRODUCT DESCRIPTION", type text}, {"DATE", type text}, {"Price", type text}, {"Promo Day", Int64.Type}, {"WANTED OUTCOME", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PROMO ID"}, {{"GroupedTable", each _, type table [PROMO ID=nullable number, RETAILER=nullable text, STORE FORMAT=nullable text, BRANCH NUMBER=nullable text, STORE NAME=nullable text, RETAILER_PRODUCT_CODE=nullable number, Client=nullable text, PRODUCT DESCRIPTION=nullable text, DATE=nullable text, Price=nullable text, Promo Day=nullable number, WANTED OUTCOME=nullable text]}}),
#"Filled Up" = Table.FillUp(#"Grouped Rows",{"GroupedTable"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"PROMO ID"}),
#"Expanded GroupedTable" = Table.ExpandTableColumn(#"Removed Columns", "GroupedTable", {"PROMO ID", "RETAILER", "STORE FORMAT", "BRANCH NUMBER", "STORE NAME", "RETAILER_PRODUCT_CODE", "Client", "PRODUCT DESCRIPTION", "DATE", "Price", "Promo Day", "WANTED OUTCOME"}, {"PROMO ID", "RETAILER", "STORE FORMAT", "BRANCH NUMBER", "STORE NAME", "RETAILER_PRODUCT_CODE", "Client", "PRODUCT DESCRIPTION", "DATE", "Price", "Promo Day", "WANTED OUTCOME"})
in
#"Expanded GroupedTable"
Also, attached the pbix file as reference.
If 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 let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi,
this dax would replace the empty price with the next days price with the same promo id. If its empty it will still show empty.
Do you need the rest of the prices filled too? If so with which value?
if(Tabelle1[Price]= BLANK(),LOOKUPVALUE(Tabelle1[Price],Tabelle1[PROMO ID],Tabelle1[PROMO ID],Tabelle1[DATE],DATEADD(Tabelle1[DATE],1,DAY)),Tabelle1[Price])
Hi,
you can try this way:
- In power query select the column Price,
- substitute space with null
- then fill up
and you obtain:
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !
The problem i will have with that is if Day 21 of PROMO 1 is blank and Day 1 of PROMO 2 is not it will fill promo 1 with the wrong value.
Need this to work per promo ID
Sorry,
if you want to do that after replace you have to group by:
then modify in advanced editor the red part of the code:
{{"AllRows", each _, type table
With this:
{{"AllRows", each Table.FillDown( Table.FillUp(_,{"Price"}),{"Price"}), type table
and it function.
You can refer to this video by Courbal
https://www.youtube.com/watch?v=QV98Jndj5Fo
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !
User | Count |
---|---|
66 | |
46 | |
20 | |
19 | |
15 |
User | Count |
---|---|
121 | |
41 | |
38 | |
28 | |
23 |