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 do...
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

v-luwang-msft's avatar
v-luwang-msft
Community 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