Sumifs with variable selection

[Anonymous]

Hello all,

I am new to this forum and to Power Query.

My name is Stefano and I am a quite proficient excel users which is trying ti migrate to expand his knowledge to Power Query and Power BI.

I have recently created a an excel file which, give date, time of day and operating times, generates a normal distribution to split a daily volume into hourly components.

I did so in a table thorugh the use of NORM.DIST function and some support columns.

below a visual example:

I have progress in creating a query up to column "Hours Trading".

I am currently stuck in creating the "Marker" column, in excel I have used the below formula:

=IF([@[Hours Trading]]=0,"",SUMIFS($F$7:$F7,$A$7:$A7,[@Date],$C$7:$C7,C7))

the arguments in bold expand as the formula is dragged down the table.

The intention is to create a increasing value for each hour in which the store will be trading.

If on 1/01/2021 at 8:00 the store is trading and is the first hour of trade return 1, at 9:00 would return 2, and so on until the last hour of the trade of the day.

I don't know how to achieve the above in Power Query.

on a more general note:

the columns "Marker", "Median" and "Standard Deviation" are then used to generate a "Standard Distribution" which is refined to be always 100% across the day.

"Median" is simply the average of all "Marker" Values and "Standard Deviation" is taken from an additional table which i setup manually according to the values which give me the best shape of the bell curve.

The ultimate goal is to achieve a query that automatically generates a standard distribution between the times trade which are an input from a table in excel.

Could anyone help me in achieving this end result and explain me how to do it?

below the code from the advanced editor of what achieved so far in my query:

let
Source = Table.NestedJoin(Date, {"Local Key"}, Time, {"Local Key"}, "Time", JoinKind.LeftOuter),
#"Expanded Time" = Table.ExpandTableColumn(Source, "Time", {"Time"}, {"Time.Time"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Time", {"Local Key"}, Store, {"Local Key"}, "Store", JoinKind.LeftOuter),
#"Expanded Store" = Table.ExpandTableColumn(#"Merged Queries", "Store", {"Store"}, {"Store.Store"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Store",{{"Time.Time", "Time"}, {"Store.Store", "Store"}}),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Date", "Store"}, Store_Trading_Times, {"Date", "Store"}, "Store_Trading_Times", JoinKind.LeftOuter),
#"Expanded Store_Trading_Times" = Table.ExpandTableColumn(#"Merged Queries1", "Store_Trading_Times", {"Open Trade", "Close Trade"}, {"Store_Trading_Times.Open Trade", "Store_Trading_Times.Close Trade"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Store_Trading_Times",{{"Store_Trading_Times.Open Trade", "Open Trade"}, {"Store_Trading_Times.Close Trade", "Close Trade"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Hours Trading", each if[Time]>=[Open Trade] and [Time]<[Close Trade] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each true)
in
#"Filtered Rows"

thank you in advance!

Regards,

Stefano

[Anonymous · Accepted solution]

Hi @Anonymous ,

I completed it in Power Query, you can download the attachment to view the details.

I recommend you to create calculated columns in Power BI Desktop, which is easier. You can also check it in the attachment.

Marker calculated column = IF([Time]<[End Trading]&&[Time]>=[Start Trading],RANKX(FILTER('Table',[Date]=EARLIER('Table'[Date])),'Table'[Time],,ASC,Dense)-HOUR([Start Trading]))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You may need to "unlearn"  some of your Excel knowledge, and embrace Power Query's way of thinking.

Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.