Forum Discussion
Solved
Multiplying 2 tables based on a date
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...
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"
| 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"
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
- Awesome! I am still waiting for that MAGIC function in DAX... 🙂