Create new table, calculate standard deviation

Power BI Data Model
1

Hello!

Our aim is to create a new table out of two following tables.

Table 1 / Date Table

Table 2 / Fact Table

As we would like to calculate the standard deviation per item, we desire to create a table which contains the amount per item and year-month for items with no amount we need Zero Values per year-month.

Please see example and sample in the annex:

Thank you very much in advance for your help!

Best regards,

FranzsampleFile.pbix (57.2 KB)

Sample%20File
Sample File.jpg754×688 101 KB

2

Hi @Franz,

Thanks for providing the sample data in PBIX, that’s very helpful. :+1:

This sounds like a job for Power Query! I created a distinct list of Item Numbers, then added the Year-Month column from the Dates table for each and Merged that with the Fact table. The M code for the new Combined data query looks like this:

let
    Source = Table.Distinct( #"Table 2 | Fact Table"[[Item Number]]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #"Table 1 | Date Table"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {" Year-Month"}, {" Year-Month"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Item Number", " Year-Month"}, #"Table 2 | Fact Table", {"Item Number", "Year-Month"}, "Table 2 | Fact Table", JoinKind.LeftOuter),
    #"Expanded Table 2 | Fact Table" = Table.ExpandTableColumn(#"Merged Queries", "Table 2 | Fact Table", {"Amount"}, {"Amount"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table 2 | Fact Table",null,0,Replacer.ReplaceValue,{"Amount"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{" Year-Month", type text}})
in
    #"Changed Type"

And here’s the result:

image
image.png523×793 9.48 KB

I hope this is helpful. Attached the updated PBIX.
eDNA - Create new table, calculate standard deviation.pbix (69.8 KB)

1 Like
3

Hi Melissa, thanks, works perfect :grinning: