cancel
Showing results for
Did you mean:

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

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

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 !

1 ACCEPTED SOLUTION
Super User

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
_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

11 REPLIES 11
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Greetings !

Super User

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
_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

Helper II

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

Super User

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

Super User

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}}),
#"Expanded NextTime" = Table.ExpandTableColumn(#"Merged Queries", "NextTime", {"Date&Time"}, {"Date&Time.Next"}),
#"Filled Down" = Table.FillDown(#"Expanded NextTime",{"Date&Time.Next"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Minutes",type number}}),
#"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),
#"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.:

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!

Helper II

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!

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

Thank you !
Helper II

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 !

Super User

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

Helper II
Hi @AIB !

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

Greetings !