Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 !
Solved! Go to 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] ) )
So, where do the numbers in the "Wanted Table" come from?
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).
Greetings !
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] ) )
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
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.:
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
Could you help me?
I really appreciate your attention in helping and @AlB.
Cheers!
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 @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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
85 | |
65 | |
51 | |
45 |
User | Count |
---|---|
217 | |
88 | |
81 | |
65 | |
56 |