Create new summarized table and skip blanks

[CornelisV]

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
09:00
10:00

And the final output must be a new table, without discarding the table above:

TS	Phase
02:00	Start
06:00	End

Could you please demonstrate how to resolve?

Best regards,

Cornelis

[v-xiaocliu-msft · Accepted solution]

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 · Accepted solution]

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]

Hello @Ashish_Mathur ,

Thank you for your solution, the UnpivotOtherColums is doing the job and subsequently the distinct step.

Best regards,

Cornelis

[Ashish_Mathur]

You are welcome.

[v-xiaocliu-msft · Accepted solution]

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

[CornelisV]

Hello @v-xiaocliu-msft ,

Clearly a different approach. It works, thank you.

Best regards,

Cornelis

[FreemanZ]

hi @CornelisV ,

In Power Query, try like:

merge phase1 and phase2 columms

select the merged columm, remove blank and remove duplicate rows

[Bibiano_Geraldo]

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]

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]

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]

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