Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Creating new table out of existing one and grouping by time

Hi.

 

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


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.

 

table.png

 

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

 

Greetings !

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

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   www.daxformatter.com

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler !

 

Sorry if I was not clear on the explanation ... Smiley Embarassed
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
Community Champion
Community Champion

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   www.daxformatter.com

Anonymous
Not applicable

Hi @AlB  Robot Happy

 

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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.:

 

image.png

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

 

resultsWeightTime.png

Could you help me?

 

I really appreciate your attention in helping  and @AlB.

Cheers!

Anonymous
Not applicable

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

Thank you !
Anonymous
Not applicable

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
Community Champion
Community Champion

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
Not applicable

Hi @AIB !

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

Greetings !

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.