Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Repeat every value for each id

Hi,   Is there a way to easily convert a table to list all values for each id?   This is the solution I'm looking for:   Thank you in advance!   Julia
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Please following these steps:

     

    1. Add a column whose value is 1 and a column which group ID and Type in table:

    Value = 1 
    IDType =
[ID] & [Type]

     

    2. Create a calculated table using the following formula:

    Table 2 =
VAR _tab =
    ADDCOLUMNS (
        CROSSJOIN ( ALL ( 'Table'[ID] ), ALL ( 'Table'[Type] ) ),
        "IDType", [ID] & [Type]
    )
RETURN
    ADDCOLUMNS (
        _tab,
        "Re",
            VAR _val =
                LOOKUPVALUE ( 'Table'[Value], 'Table'[IDType], [IDType] )
            RETURN
                IF ( _val = BLANK (), 0, _val )
    )

    My final output looks like this:

     

    Here is the pbix file.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,
    Eyelyn Qin

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , You can create using

crossjoin(all(table[id]), Table(Name))

 

but to get 1, 0 more stuff is needed

like -union(addcolumns(Table, "status", 1), addcolumns(except(crossjoin(all(table[id]), Table(Name)), Table), "status", 0))