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:

TSPhase 1Phase2
01:00  
02:00Start 
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:

TSPhase
02:00Start
06:00

End

 

Could you please demonstrate how to resolve?

 

Best regards,

 

Cornelis

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.

     

10 Replies

  • 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]))
    )
)

         

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

    hi CornelisV ,

     

    In Power Query, try like:

     

    merge phase1 and phase2 columms

    select the merged columm, remove blank and remove duplicate rows

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

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

      Hello Anonymous ,

       

      Clearly a different approach. It works, thank you.

       

      Best regards,

       

      Cornelis

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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

      Hello Ashish_Mathur ,

       

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

       

      Best regards,

       

      Cornelis