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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
aBerg
Helper II
Helper II

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

Hi @aBerg

 

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 !

Hi @aBerg

 

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

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

Hi @aBerg 

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

Cheers 

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

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!

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

Thank you !

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 !

Hi @aBerg

'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

Hi @AIB !

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

Greetings !

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.