DAX - Create a new table that repeats the value based on another column

[Pedro_Okazaki]

Dear PBI users, good morning.

I need a help with this issue, I would like to create a new table using dax that would repeat the value of another column based on another. To exemplify:

Current table:

Product	Pallet Quantity
A	3
B	2

Desired Result:

Product	Pallet Quantity
A	1
A	1
A	1
B	1
B	1

How can I do that in DAX?

Thank you in advance!

[Greg_Deckler · Accepted solution]

@Pedro_Okazaki Try this. PBIX is attached below signature.

Table 2 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GENERATE(
                DISTINCT('Table'[Product]),
                GENERATESERIES(1,MAXX(FILTER('Table',[Product] = EARLIER('Table'[Product])),[Pallet Quantity]),1)
            ),
            "Pallet Quantity",1
        ),
        "Product",[Product],
        "Pallet Quanitity",[Pallet Quantity]
    )

[Ahmedx]

also possible in power query

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhjeqxmvgzEItNzcu?e=frRM7C

[Pedro_Okazaki]

Is there a way that I could include Indexes as well? 

For example 

Product - Pallet Quantity - Index

A            - 1                       - 1

A            - 1                       - 2

A            - 1                       - 3

B            - 1                       - 1

B            - 1                       - 2

[Greg_Deckler · Accepted solution]

@Pedro_Okazaki Try this. PBIX is attached below signature.

Table 2 = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            GENERATE(
                DISTINCT('Table'[Product]),
                GENERATESERIES(1,MAXX(FILTER('Table',[Product] = EARLIER('Table'[Product])),[Pallet Quantity]),1)
            ),
            "Pallet Quantity",1
        ),
        "Product",[Product],
        "Pallet Quanitity",[Pallet Quantity]
    )