Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Fill with same value based on index column or minimize table to one column

Hi everyone, 

 

I am trying to solve the following problem where I have a table similar to the following one (in Power Query Editor):

I would like to get two of the following result but I don't know how to get it:

 

Thanks in advance.

Fill
Null
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi Anonymous ，

    Create new table:

    Table 2 = 
SELECTCOLUMNS (
    'Table',
    "UID", 'Table'[UID],
    "Date1",
        CALCULATE (
            MAX ( 'Table'[DATA1] ),
            FILTER ( 'Table', 'Table'[UID] = EARLIER ( 'Table'[UID] ) )
        ),
    "Date2",
        CALCULATE (
            MAX ( 'Table'[DATA2] ),
            FILTER ( 'Table', 'Table'[UID] = EARLIER ( 'Table'[UID] ) )
        ),
    "Date3",
        CALCULATE (
            MAX ( 'Table'[DATA3] ),
            FILTER ( 'Table', 'Table'[UID] = EARLIER ( 'Table'[UID] ) )
        )
)

     

    Table 3 = DISTINCT('Table 2')

     

     

    Best Regards

    Lucien

1 Reply

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous ，

    Create new table:

    Table 2 = 
SELECTCOLUMNS (
    'Table',
    "UID", 'Table'[UID],
    "Date1",
        CALCULATE (
            MAX ( 'Table'[DATA1] ),
            FILTER ( 'Table', 'Table'[UID] = EARLIER ( 'Table'[UID] ) )
        ),
    "Date2",
        CALCULATE (
            MAX ( 'Table'[DATA2] ),
            FILTER ( 'Table', 'Table'[UID] = EARLIER ( 'Table'[UID] ) )
        ),
    "Date3",
        CALCULATE (
            MAX ( 'Table'[DATA3] ),
            FILTER ( 'Table', 'Table'[UID] = EARLIER ( 'Table'[UID] ) )
        )
)

     

    Table 3 = DISTINCT('Table 2')

     

     

    Best Regards

    Lucien