Forum Discussion

CornelisV's avatar
CornelisV
Icon for Helper IV rankHelper IV
1 year ago
Solved

Create new summarized table and skip blanks

Dear all,   I have a table with 3 columns: TS Phase 1 Phase2 01:00     02:00 Start   03:00     04:00     05:00     06:00   End 07:00   End 08:00   End ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi CornelisV ,

     

    In your example, if you only want to keep the first record where the value of the phase column is "end", please try this

    Table 2 = 
var _startTime =MAXX( FILTER( 'Table',[Phase 1]="Start"),[TS])
var _endTime = CALCULATE(MIN('Table'[TS]),  FILTER('Table',[Phase2]="End"&&[TS]>=_startTime))
RETURN
     SELECTCOLUMNS(
        {
            (_startTime, "Start"),
            (_endTime, "End")
        },
        "Ts", [Value1],
        "Phase", [Value2]
    )

     

     

    Best Regards,

    Wearsky

  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"TS"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"TS", type time}})
in
    #"Changed Type"

    Hope this helps.

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

HI CornelisV ,

Create a new table in Power BI using the following DAX formula:

FinalTable = 
FILTER(
    UNION(
        SELECTCOLUMNS(
            FILTER(YourOriginalTable, NOT(ISBLANK(YourOriginalTable[Phase 1]))),
            "TS", YourOriginalTable[TS],
            "Phase", YourOriginalTable[Phase 1]
        ),
        SELECTCOLUMNS(
            FILTER(YourOriginalTable, NOT(ISBLANK(YourOriginalTable[Phase 2]))),
            "TS", YourOriginalTable[TS],
            "Phase", YourOriginalTable[Phase 2]
        )
    ),
    NOT(ISBLANK([Phase]))
)

 

Note: Make sure to change tables and columns names with your owns.
If this answer help you, please give a Kudo and mark as solution.

 

Thank you

  • CornelisV's avatar
    CornelisV
    Icon for Helper IV rankHelper IV
    1 year ago

    Hi Bibiano_Geraldo 

     

    Thank you for your contribution, that looks promising.

    I have checked your solution and this is what I get:

    I suppose that DISTINCT should be added. What is your opinion?

     

    Best regards,

     

    **bleep**

    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Icon for Super User rankSuper User
      1 year ago

      Hi, 
      Adding DISTINCT can be a good idea if you want to ensure that your FinalTable does not contain duplicate rows. Here’s how you can modify your DAX formula to include DISTINCT:

       

      FinalTable = 
DISTINCT(
    FILTER(
        UNION(
            SELECTCOLUMNS(
                FILTER(YourOriginalTable, NOT(ISBLANK(YourOriginalTable[Phase 1]))),
                "TS", YourOriginalTable[TS],
                "Phase", YourOriginalTable[Phase 1]
            ),
            SELECTCOLUMNS(
                FILTER(YourOriginalTable, NOT(ISBLANK(YourOriginalTable[Phase 2]))),
                "TS", YourOriginalTable[TS],
                "Phase", YourOriginalTable[Phase 2]
            )
        ),
        NOT(ISBLANK([Phase]))
    )
)

       

      • CornelisV's avatar
        CornelisV
        Icon for Helper IV rankHelper IV
        1 year ago

        Hello Bibiano_Geraldo ,

         

        Yes, that is something what I have thought.:

         

        Table = DISTINCT(FILTER(
            UNION(
                SELECTCOLUMNS(
                    FILTER('Sheet1', NOT(ISBLANK('Sheet1'[Phase 1]))),
                    "TS", 'Sheet1'[TS],
                    "Phase", 'Sheet1'[Phase 1]
                ),
                SELECTCOLUMNS(
                    FILTER('Sheet1', NOT(ISBLANK('Sheet1'[Phase2]))),
                    "TS", 'Sheet1'[TS],
                    "Phase", 'Sheet1'[Phase2]
                )
            ),
            (NOT(ISBLANK([Phase]))
        )))
         
        However, it does not remove multiple rows.
         
        Best regards,
         
        Cornelis