Creating new table out of existing one and grouping by time

[Anonymous]

Hi.

I would like help with an application in PBI Desktop. I don't know how to start... 

I have to create a simple line chart to compare an "Energy measure" (fMP table) with a "Weight/Time measure" (fMPPr table), however the granularity of the "fMP table" is each 15 minutes and the data in the "fMPPr table" is per event, that is, each row has a time interval of hours&minutes.
I need to compare two measurements at the same time base (15 minutes granularity), so I first have to divide each row into several rows of minute granularity and then group the data by 15 minutes (same granularity as the fMP table). I created a drawing demonstrating my need and also the .pbix application link .PBIX.

If anyone can help in how to do it I thank you immensely. I have no idea how to start ... 

Greetings !

[Greg_Deckler]

So, where do the numbers in the "Wanted Table" come from?

[Anonymous]

Hi @Greg_Deckler !

Sorry if I was not clear on the explanation ... 
The "time column" is grouped by 15 minutes and the "value column" is the sum of values in 15 minutes (50.16 x 15).

Here the table 

Greetings !

[AlB · Accepted solution]

Hi @Anonymous

You can create a new calculated table as follows. It's a long piece of code. I'm sure it can be done in a shorter fashion but I ain't yet had time to trim it down. The columns of interest are [Expanded Date/Time] and [Weight/Time per 15 mins]. You can further manipulate the resulting table to select only specific columns or change their name if required.

NewTable2 = 
FILTER (
    VAR _OneMinute = 1 / 24 / 60
    VAR _ExpandedTable =
        GENERATE (
            fMPPr;
            VAR _CurrentTime = fMPPr[Date&Time]
            VAR _NextTimeAux =
                CALCULATE (
                    MIN ( fMPPr[Date&Time] );
                    ALL ( fMPPr );
                    fMPPr[Date&Time] > EARLIER ( fMPPr[Date&Time] )
                )
            VAR _NextTime =
                IF ( NOT ISBLANK ( _NextTimeAux ); _NextTimeAux; _CurrentTime ) //The IF is to avoid expansion in the very last Date/Time 
            VAR _ResultTable =
                SELECTCOLUMNS (
                    GENERATESERIES ( _CurrentTime; _NextTime; _OneMinute );
                    "Expanded Date/Time"; [Value];
                    "Expanded Weight/Time"; [Weight/Time]
                )
            RETURN
                _ResultTable
        )
    RETURN
        ADDCOLUMNS (
            _ExpandedTable;
            "Weight/Time per 15 mins"; IF (
                MINUTE ( [Expanded Date/Time] ) IN { 0; 15; 30; 45 };
                SUMX (
                    FILTER (
                        _ExpandedTable;
                        DATEDIFF ( [Expanded Date/Time]; EARLIER ( [Expanded Date/Time] ); MINUTE ) >= 1
                            && DATEDIFF ( [Expanded Date/Time]; EARLIER ( [Expanded Date/Time] ); MINUTE ) <= 15
                    );
                    [Expanded Weight/Time]
                )
            )
        );
    NOT ISBLANK ( [Weight/Time per 15 mins] )
)

Code formatted with  

[Anonymous]

Hi @AlB  

I am curious if it is possible to do the same function in Power Query in M code? I think in this way there would be no need to create a new table (2nd table). Do you think it is possible ? Could you guide me on this path please? I'm having problem with DAX solution sometimes the message "working on it" is shown forever ... 

Thank you very much for your help and attention.

Cheers

[AlB]

Hi @Anonymous 

I'm sure it can but either @ImkeF  or @Nolock  will be able to guide you on this better that i would

Cheers 

[ImkeF]

Hi this would be an M-solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdVtbsMgDAbgq0z9XVHbYD58lar3v8aMaaAFKqR1i8ir5Jkx7vN5w9v9BuEB9CDA8odeqAh6XQ36ofrRdf2DNenrItZVjDVS1+wuQSJXb3p4ID0wF30Wig8C8PWG6XUjwuD0ma/70176mQmiP542pA4xMFwishzE7HASkWA8iEYkFFfYRH7KsACJTxvR5WgVtF/1dq4RDN75CeT1VQfQiGiJcjJQWECBBX+BzIF2h68ahUZCdnkhBX8kXRElRTIST5koWIR5Q+oQ5HdpfDGDNZOnZdeCEB9EIxKyK2iiOGWSQJAAG9HlsAsefVRajcDBJNJ35YNoROq2tc5OiyioqPwQGcTam6xIsbd8Rr8cNhY+ddKIKAmKkfKUyXrShHfnv0Osz1pjWV2sVBBoqVIUOlVpRJSkj6ukOaPzSElxQ+oQu+LUSbmRgisTKQmemntElOS9kRAWE+uB2/VSl2C+JGayf4CAo+PFxOlouiJq0vFmpml0E+hIEtzVqUuwH7TPQbkZ3doGp60bkdpNrcFxmt2Eol1nHTKbuoRsx/pgerdTLEudivhTh4+IDu/chjdO05tIIG2/T4aE8JpRrU5vk3fp21RrfhhNH5H6hdJGE4bFpBOVNtNySOyKuwnzj73Tmp/G5Uekfu3mZpomOClIz134YTIJ1dXSt66RMm9IfCYxfw2n1z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, DateSys = _t, Day = _t, Month = _t, Year = _t, Start_Hour = _t, Start_Minute = _t, End_Hour = _t, End_Minute = _t, Minutes_Production = _t, Seconds_Production = _t, Weight = _t, Hours = _t, Date = _t, #"Date&Time" = _t, #"Weight/Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"DateSys", type datetime}, {"Day", Int64.Type}, {"Month", Int64.Type}, {"Year", Int64.Type}, {"Start_Hour", Int64.Type}, {"Start_Minute", Int64.Type}, {"End_Hour", Int64.Type}, {"End_Minute", Int64.Type}, {"Minutes_Production", Int64.Type}, {"Seconds_Production", Int64.Type}, {"Weight", Int64.Type}, {"Hours", type datetime}, {"Date", type date}, {"Date&Time", type datetime}, {"Weight/Time", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.2", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.2"}, #"Added Index1", {"Index.1"}, "NextTime", JoinKind.LeftOuter),
    #"Expanded NextTime" = Table.ExpandTableColumn(#"Merged Queries", "NextTime", {"Date&Time"}, {"Date&Time.Next"}),
    #"Filled Down" = Table.FillDown(#"Expanded NextTime",{"Date&Time.Next"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Minutes", each ([#"Date&Time.Next"]-[#"Date&Time"])*24*60),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Minutes",type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "ListOfMinutes", each {1..[Minutes]}),
    #"Expanded ListOfMinutes" = Table.ExpandListColumn(#"Added Custom1", "ListOfMinutes"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded ListOfMinutes",null,0,Replacer.ReplaceValue,{"ListOfMinutes"}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Value", "MinuteDateTime", each [#"Date&Time"] + #duration(0,0,[ListOfMinutes] - 1,0) as datetime),
    #"Inserted Time" = Table.AddColumn(#"Added Custom2", "Time", each DateTime.Time([MinuteDateTime]), type time),
    #"Added Custom3" = Table.AddColumn(#"Inserted Time", "15MinuteInterval", each Time.From(Number.RoundUp(Number.From([Time])*(24*4),0)/(24*4))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Interval", each DateTime.From(Date.From([MinuteDateTime]) & [15MinuteInterval])),
    #"Grouped Rows" = Table.Group(#"Added Custom4", {"Date&Time", "Interval"}, {{"Weight/Time", each List.Sum([#"Weight/Time"]), type number}})
in
    #"Grouped Rows"

But there seems to be an issue with the sequence of your data. Maybe that's why you got a problem with the DAX-solution.:

[Anonymous]

Hello @ImkeF !

Thanks for the feedback. I don't know if I missed something, but the resulting value "Weight/Time" with M (table Query1) is different from the result in DAX "Weight/Time per 15 mins".

https://1drv.ms/u/s!Ag06VMKBGvfCjWYNcikAqrfO5_Jm

Could you help me?

I really appreciate your attention in helping  and @AlB.

Cheers!

[Anonymous]

Hi.
Someone else can help, please ?
I could not find why M code results in different manner of DAX code ?

Thank you !

[Anonymous]

Hi @AlB !

Sorry to return to the same issue, but I'm having a problem to understand from where is the [value] column. 

Could you please tell me where does the [value] ("Expanded Date / Time") column come from? I cannot find this column in any of the tables ... 

VAR _ResultTable =
                SELECTCOLUMNS (
                    GENERATESERIES ( _CurrentTime; _NextTime; _OneMinute );
                    "Expanded Date/Time"; [Value];
                    "Expanded Weight/Time"; [Weight/Time]
                )
            RETURN
                _ResultTable

Greetings !

[AlB]

Hi @Anonymous, 

'Value' is the name of the column in the table created by GENERATESERIES(  ).

Try creating a new calculated table, for instance: 

      NewTable = GENERATESERIES(1,10)

and see the result

Cheers

[Anonymous]

Hi @AIB !

Thanks so much for the quick reply.
I will try that !

Greetings !