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 IDRETAILERSTORE FORMATBRANCH NUMBERSTORE NAMERETAILER_PRODUCT_CODEClientPRODUCT DESCRIPTIONDATEPricePromo DayWANTED OUTCOME1Retailer 1Format 1Store 1Store Name 11001Client 1Product 121/04/2022 1£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 122/04/2022£1.502£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 123/04/2022£1.503£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 124/04/2022 4£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 125/04/2022£1.505£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 126/04/2022£1.506£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 127/04/2022£1.507£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 128/04/2022£1.508£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 129/04/2022£1.509£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 130/04/2022£1.5010£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 101/05/2022 11£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 102/05/2022 12£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 103/05/2022 13£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 104/05/2022£1.5014£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 105/05/2022£1.5015£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 106/05/2022 16£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 107/05/2022£1.5017£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 108/05/2022 18£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 109/05/2022£1.5019£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 110/05/2022£1.5020£1.501Retailer 1Format 1Store 1Store Name 11001Client 1Product 111/05/2022£1.5021£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 121/04/2022 1£2.002Retailer 1Format 1Store 2Store Name 21001Client 1Product 122/04/2022£2.002£2.002Retailer 1Format 1Store 2Store Name 21001Client 1Product 123/04/2022£1.503£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 124/04/2022£1.504£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 125/04/2022£1.505£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 126/04/2022£1.506£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 127/04/2022£1.507£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 128/04/2022£1.508£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 129/04/2022£1.509£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 130/04/2022£1.5010£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 101/05/2022£1.5011£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 102/05/2022£1.5012£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 103/05/2022£1.5013£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 104/05/2022£1.5014£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 105/05/2022£1.5015£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 106/05/2022£1.5016£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 107/05/2022£1.5017£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 108/05/2022£1.5018£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 109/05/2022£1.5019£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 110/05/2022£1.5020£1.502Retailer 1Format 1Store 2Store Name 21001Client 1Product 111/05/2022£1.5021£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 121/04/2022£1.501£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 122/04/2022£1.502£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 123/04/2022 3£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 124/04/2022 4£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 125/04/2022£1.505£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 126/04/2022£1.506£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 127/04/2022£1.507£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 128/04/2022 8£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 129/04/2022£1.509£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 130/04/2022 10£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 101/05/2022 11£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 102/05/2022£1.5012£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 103/05/2022£1.5013£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 104/05/2022£1.5014£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 105/05/2022£1.5015£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 106/05/2022 16£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 107/05/2022 17£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 108/05/2022£1.5018£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 109/05/2022£1.5019£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 110/05/2022 20£1.503Retailer 1Format 1Store 3Store Name 31001Client 1Product 111/05/2022 21£1.50

Forum Discussion

Frequent Visitor

4 years ago

Solved

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

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

Filling up and down by catergory.pbix37 KB

5 Replies

serpiva64
Solution Sage
4 years ago
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
  Frequent Visitor
  4 years ago
  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
    Solution Sage
    4 years ago
    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 !
random_Bi_User
Frequent Visitor
4 years ago
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!

v-cazheng-msft

Community Support

4 years ago

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.

Best Regards,

Community Support Team _ Caiyun

Filling up and down by catergory.pbix37 KB

Forum Discussion

Filling up and down by catergory

5 Replies

Related content

Filled map

Filled map

Filled maps

Maps with both filled polygons and points

Map and filled map visuals are disabled