Forum Discussion
Multiplying 2 tables based on a date
lokeswer - OK, in DAX you would do a CROSSJOIN I believe, like this:
New Table = CROSSJOIN('Table','Table2')You could create your calculated column there or you could do it in the formula by adding ADDCOLUMNS.In Power Query, you do a cross join like this:lokeswer - OK, in DAX you would do a CROSSJOIN I believe, like this:
New Table = CROSSJOIN('Table','Table2')You could create your calculated column there or you could do it in the formula by adding ADDCOLUMNS.In Power Query, you do a cross join like this:let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NtA3MjAyUNJRMjQwAFNAWilWJ1rJVN8QJmUBljGFSxgh6wHRZiAqNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Production = _t, Usage = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Production", Int64.Type}, {"Usage", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table2),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Inputs", "BOM%"}, {"Custom.Inputs", "Custom.BOM%"})
in
#"Expanded Custom"
Probably. But, is that actually how your data looks? Exactly what does your data look like?
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Also, are you trying to do this during import or are you trying to do this after import?
Inputs BOM% Shred 0.16 RS 0.13 P10 0 WC1 0.14 WC2 0.13 WC3 0.17 RAR 0.25 Hams 0.02 Date Usage Thursday, April 30, 2020 297,379 Friday, May 1, 2020 375,637 Saturday, May 2, 2020 375,637 Sunday, May 3, 2020 - Monday, May 4, 2020 375,637 Tuesday, May 5, 2020 375,637 Wednesday, May 6, 2020 375,637 Thursday, May 7, 2020 297,379 Thanks Greg_Deckler for chiming in,
This is how the dataset look like.
I'm trying to do this during import - (obviously i cleaned up the usage data)
lokeswer - OK, in DAX you would do a CROSSJOIN I believe, like this:
New Table = CROSSJOIN('Table','Table2')You could create your calculated column there or you could do it in the formula by adding ADDCOLUMNS.In Power Query, you do a cross join like this:lokeswer - OK, in DAX you would do a CROSSJOIN I believe, like this:
New Table = CROSSJOIN('Table','Table2')You could create your calculated column there or you could do it in the formula by adding ADDCOLUMNS.In Power Query, you do a cross join like this:let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NtA3MjAyUNJRMjQwAFNAWilWJ1rJVN8QJmUBljGFSxgh6wHRZiAqNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Production = _t, Usage = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Production", Int64.Type}, {"Usage", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table2),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Inputs", "BOM%"}, {"Custom.Inputs", "Custom.BOM%"})
in
#"Expanded Custom"Greg_Deckler This works Magic, made the whole week. Thanks much
