Filling up and down by catergory

[michaeldonnelly]

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

[v-cazheng-msft · Accepted 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

[random_Bi_User]

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])

Also feel free to add more congruities in the lookupvalue. Hope this helps!

[serpiva64]

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 !

[michaeldonnelly]

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

[serpiva64]

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 !