Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Repeat subset for every value in the main column

Hi all.

 

I find it difficult to explain what I am looking for exactly, so I will try to show you with an example.

 

I have a table like this one with multiple columns, one of which we will consider to be the main one for our purpose.

 

 

I want to take those rows with blanks in the main column and repeat them for each value of the main column, resulting in a table like this one:

 

 

I do not know if this procedure has a name. Due to certain limitations I can not use Python for the task.

 

Any help is really much appreciated.

 

Thanks!

 

General Comment
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Anonymous,

     

    Try this calculated table.

     

    Assumptions:

    Table1 is the original table.

    Table1[Main Column] values that appear blank are actually blank and not empty strings. If they are empty strings, add a Power Query step that replaces empty strings with null (see code at end of post).

     

    Table2 = 
VAR vNewRows =
    GENERATE (
        CALCULATETABLE (
            DISTINCT ( Table1[Main Column] ),
            Table1[Main Column] <> BLANK ()
        ),
        CALCULATETABLE (
            SUMMARIZE (
                Table1,
                Table1[Column 1],
                Table1[Column 2],
                Table1[Column 3],
                Table1[Column 4]
            ),
            Table1[Main Column] = BLANK ()
        )
    )
VAR vExistingNonBlankRows =
    FILTER ( Table1, Table1[Main Column] <> BLANK () )
VAR vResult =
    UNION ( vNewRows, vExistingNonBlankRows )
RETURN
    vResult

     

    If necessary, use this Power Query step to replace empty strings with null:

     

    Table.ReplaceValue(ChangedType,"",null,Replacer.ReplaceValue,{"Main Column"})

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    Anonymous,

     

    Try this calculated table.

     

    Assumptions:

    Table1 is the original table.

    Table1[Main Column] values that appear blank are actually blank and not empty strings. If they are empty strings, add a Power Query step that replaces empty strings with null (see code at end of post).

     

    Table2 = 
VAR vNewRows =
    GENERATE (
        CALCULATETABLE (
            DISTINCT ( Table1[Main Column] ),
            Table1[Main Column] <> BLANK ()
        ),
        CALCULATETABLE (
            SUMMARIZE (
                Table1,
                Table1[Column 1],
                Table1[Column 2],
                Table1[Column 3],
                Table1[Column 4]
            ),
            Table1[Main Column] = BLANK ()
        )
    )
VAR vExistingNonBlankRows =
    FILTER ( Table1, Table1[Main Column] <> BLANK () )
VAR vResult =
    UNION ( vNewRows, vExistingNonBlankRows )
RETURN
    vResult

     

    If necessary, use this Power Query step to replace empty strings with null:

     

    Table.ReplaceValue(ChangedType,"",null,Replacer.ReplaceValue,{"Main Column"})