Forum Discussion
baribir
Helper I
Helper ICreate a new table from two other tables
Hi All,
I need to make a new table from the contents of two other tables with DAX. And add a column with the time difference(Date1-Date2).
Table 1:
| ID1 | Date1 |
| 1 | 05-12-18 |
| 2 | 04-12-18 |
| 3 | 04-12-18 |
| 4 | 04-12-18 |
Table2:
| ID2 | Date2 |
| 1 | 20-11-18 |
| 2 | 27-11-18 |
| 3 | 04-12-18 |
| 4 | 11-12-18 |
A new table:
| ID1 | Date1 | ID2 | Date2 | Diff |
| 1 | 05-12-18 | 1 | 20-11-18 | 15 |
| 1 | 05-12-18 | 2 | 27-11-18 | 8 |
| 1 | 05-12-18 | 3 | 04-12-18 | 1 |
| 1 | 05-12-18 | 4 | 11-12-18 | -6 |
| 2 | 04-12-18 | 1 | 20-11-18 | 14 |
| 2 | 04-12-18 | 2 | 27-11-18 | 7 |
| 2 | 04-12-18 | 3 | 04-12-18 | 0 |
| 2 | 04-12-18 | 4 | 11-12-18 | -7 |
| 3 | 04-12-18 | 1 | 20-11-18 | 14 |
| 3 | 04-12-18 | 2 | 27-11-18 | 7 |
| 3 | 04-12-18 | 3 | 04-12-18 | 0 |
| 3 | 04-12-18 | 4 | 11-12-18 | -7 |
| 4 | 04-12-18 | 1 | 20-11-18 | 14 |
| 4 | 04-12-18 | 2 | 27-11-18 | 7 |
| 4 | 04-12-18 | 3 | 04-12-18 | 0 |
| 4 | 05-12-18 | 4 | 11-12-18 | -6 |
Hi there
Here is the DAX code
Table = CROSSJOIN('Table1','Table2')This will give you the result you want
Here we go
Table = CALCULATETABLE ( ADDCOLUMNS ( CROSSJOIN ( 'Table1', 'Table2' ), "DateDiff", DATEDIFF ( 'Table1'[Date1], 'Table2'[Date2], DAY ) ) )Hi,
Just in case you want to do this with M code, try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1TU00jUyMLRQitWJVjICCZmgCBljCpmgCcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, Date1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"Date1", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table2), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID2", "Date2"}, {"ID2", "Date2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID2", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date2", type date}}, "en-IN"), #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Custom", each [Date1]-[Date2]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}) in #"Changed Type2"You may download my PBI file from here.
Hope this helps.
10 Replies
- GilbertQ
Super User
Hi there
Here is the DAX code
Table = CROSSJOIN('Table1','Table2')This will give you the result you want
- Ashish_Mathur
Hi,
Just in case you want to do this with M code, try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1TU00jUyMLRQitWJVjICCZmgCBljCpmgCcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, Date1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"Date1", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table2), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID2", "Date2"}, {"ID2", "Date2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID2", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date2", type date}}, "en-IN"), #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Custom", each [Date1]-[Date2]), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}) in #"Changed Type2"You may download my PBI file from here.
Hope this helps.
baribir- Ashish_Mathur
You are welcome.