Forum Discussion

Helper 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

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

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

Super 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