Transpose table with calculation

[CornelisV]

Dear all,

I want to transpose the table:

Time	Phase
01:05	Start heating
05:50	End heating
06:00	Start Cooling
12:17	End cooling

Into this form:

Duration	Phase
04:45	Heating
06:17	Cooling

Tim difference between end and start heating is 04:45 hours, for cooling 06:17 hours.

Could you demonstrate how to do this?

Best regards,

Cornelis

[v-jtian-msft]

Hi,@CornelisV .Thank you for your reply.
During my testing, the date column was not really put into the calculation logic, it was in my case a reference column to generate the index. The most important logic is that the current row selected is calculated against the previous row if it is an odd number, as you mentioned, creating an index column as a marker to determine the order of calculation is the most important point, in my test, determining whether the index is odd or not is the key element, it doesn't matter whether the two rows of data being calculated are on the same day or not.
In my tests, determining whether the index is odd or not is the key element. You tried labeling the two rows of data before and after with a single index, which is also a viable solution (two rows of data with the same index are computed).
All in all, creating the right labeled columns is the key to solving your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CornelisV]

Dear Carson Jian,

Thank you for your explanation.

I agree with you and your demonstrated steps gives me a lot of inspiration for other some issues in the Power BI.

It is not very easy for me, since I'm trying to apply process data calculation, that is different in comparison to financial data treatment.

Best regards,

Cornelis

[v-jtian-msft]

Hi,Greg_Deckler ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@CornelisV . I am glad to help you.

Like this?

This is my test data:
I'm assuming that you have multiple records like this in your real data with multiple dates
 

You can add a grouped sorting sequence for calculations (based on the value of index).
This way it is easier to locate and calculate when multiple records exist at the same time.

I have created a grouping sequence (assuming that each day exists and is grouped by date)

Create the required Duration (calculated based on whether the GroupID is an odd number (End), versus the previous column of data (Start))

Filtering to remove Null rows
Getting results

Note that Power BI Desktop does not support the Duration type of data, which is displayed as a Text/Decimal number by default.

So I recalculated the column [Totals Minutes], calculated the duration of each row (in minutes)

The results in Power BI Desktop are as follows:

This is my M code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_11_11.xlsx"), null, true),
    StatesTable_Sheet = Source{[Item="StatesTable",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(StatesTable_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time", type time}, {"Phase", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"DailyGroupID", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded DailyGroupID" = Table.ExpandTableColumn(#"Grouped Rows", "DailyGroupID", {"Time", "Phase", "Index"}, {"DailyGroupID.Time", "DailyGroupID.Phase", "DailyGroupID.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded DailyGroupID",{{"DailyGroupID.Time", "Time"}, {"DailyGroupID.Phase", "Phase"}, {"DailyGroupID.Index", "GroupIndex"}}),
    CalculateDuration = Table.AddColumn(#"Renamed Columns", "Duration", each if Number.Mod([GroupIndex], 2) = 1 then Duration.From([Time] - #"Renamed Columns"[Time]{[GroupIndex]-1}) else null),
    FilteredRows = Table.SelectRows(CalculateDuration, each [Duration] <> null),
    ResultTable = Table.SelectColumns(FilteredRows, {"Duration", "Phase","Date"}),
    #"Replaced Value" = Table.ReplaceValue(ResultTable,"End heating","Heating",Replacer.ReplaceText,{"Phase"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","End cooling","Cooling",Replacer.ReplaceText,{"Phase"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Duration", type duration}}),
    #"Inserted Total Minutes" = Table.AddColumn(#"Changed Type1", "Total Minutes", each Duration.TotalMinutes([Duration]), type number)
in
    #"Inserted Total Minutes"

I shared the pbix file used for the test

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CornelisV]

Dear @v-jtian-msft and @Greg_Deckler ,

Thank you very much for thinking along!

Looking at your reply, it seems a challenging task to find a proper solution.

First at all: it is correct that more than one cooling and/or heating will occur from the same table.

Likely, the solution has one drawback: it cannot make any difference when heating start at 10th November and end  at 11th November.

What about this data, where datetime is included:

Date	Time	Date time	Phase
10-11-2024	01:05	10-11-2024 01:05	Start heating
10-11-2024	05:50	10-11-2024 05:05	End heating
11-11-2024	06:00	11-11-2024 06:00	Start Cooling
11-11-2024	12:17	11-11-2024 12:17	End cooling
11-11-2024	13:00	11-11-2024 13:00	Start heating
12-11-2024	00:29	12-11-2024 00:29	End heating

As you can see, date and time will be helpfull, but the column with date cannot be used as anchor, so a new column may be created where ID = 1,1,2,2,3,3.

 What do you think?

Best regards,

Cornelis

[Greg_Deckler · Accepted solution]

@CornelisV This will calculate the number of minutes in a column. See attached PBIX below sig.

Column = 
    VAR __Phase = [Phase]
    VAR __Current = [Date time]
    VAR __Type = 
        SWITCH( TRUE(),
            __Phase = "End heating", "Start heating",
            __Phase = "End cooling", "Start Cooling"
        )
    VAR __Result = 
        SWITCH(
            __Phase,
            "Start heating", 0,
            "Start cooling", 0,
                VAR __DateTime = [Date time]
                VAR __Result = MAXX( FILTER( ALL( 'Table' ), [Date time] < __DateTime && [Phase] = __Type ), [Date time] )
            RETURN
                __Current - __Result
        )
RETURN
    __Result * 60 * 24

[CornelisV]

Dear @Greg_Deckler ,

This is the very quick solution in one word: wow!

Thank you very much. 

One question: can you give me a clue where can I find the best documentation, url, etc how to deal with DAX programming as what you have demonstrated above? 

best regards,

Cornelis

[Greg_Deckler]

@CornelisV Are there more than just 1 instance of heating and cooling in the table? Do you want this in DAX or Power Query or do you not care?