Multiplying 2 tables based on a date

[lokeswer]

Thanks for helping me out on this. I'm one of the people making the transition from excel. 

I'm trying to get the daily consumption of a facility by input types. 

E.g each product we make is stored in a table with corresponsing dates

1/1/20 - 100

1/2/20 - 100

Input types

this data is standard for the month

a-50%

b-50%

I want to create a new table combining these 2 to get an output something like this,

1/1/20         a          50

1/1/20         b          50

1/2/20         a          50 

1/2/20         b          50

Is there a way to do it?

[Greg_Deckler]

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?

[lokeswer]

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)

[Greg_Deckler · Accepted solution]

@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"

[lokeswer]

@Greg_Deckler This works Magic, made the whole week. Thanks much

[Greg_Deckler]

Awesome! I am still waiting for that MAGIC function in DAX... 🙂