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.

     

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