Forum Discussion
Combining two tables into one with time complexity
Hello,
I'm looking for a solution for combining two tables. One [Sales_data]:
| Date | Sales |
| 01.01.2022 | 1 |
| 01.02.2022 | 2 |
| 01.03.2022 | 3 |
| 01.04.2022 | 4 |
| 01.05.2022 | 5 |
| 01.06.2022 | 6 |
| 01.07.2022 | 7 |
| 01.08.2022 | 8 |
| 01.09.2022 | 9 |
| 01.10.2022 | 10 |
| 01.11.2022 | 11 |
| 01.12.2022 | 12 |
Second [Target_sales]:
| Date | Sales |
| 01.03.2022 | 2 |
| 01.06.2022 | 5 |
| 01.09.2022 | 10 |
| 01.12.2022 | 13 |
When I merge first table with second I can't achieve targets for every month from [Sales_data] table because the targets are provided quarterly. My goal is to see following graph:
I hope that with this kid simplified data it's clear.
Thanks in advance for your help.
maati
Hi maati1980 ,
Here's a way of increasing the granularity of your target table to match your monthly sales table. I've assumed that the targets set quarterly are the monthly targets for the next 3 months:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDWMzIwMlLSUTJSitWBCJnBhEzhQpYwIUMDmJihEVzMWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}), sortASC = Table.Sort(chgTypes,{{"Date", Order.Ascending}}), addIndex1 = Table.AddIndexColumn(sortASC, "Index1", 1, 1, Int64.Type), addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type), mergeOnSelf = Table.NestedJoin(addIndex0, {"Index1"}, addIndex0, {"Index0"}, "addIndex0", JoinKind.LeftOuter), expandDate = Table.ExpandTableColumn(mergeOnSelf, "addIndex0", {"Date"}, {"dateTo"}), replaceNullDateTo = Table.ReplaceValue(expandDate,null, each Date.AddMonths([Date], 3) ,Replacer.ReplaceValue,{"dateTo"}), #"replaceDateTo-1Day" = Table.ReplaceValue(replaceNullDateTo,each [dateTo], each Date.AddDays([dateTo], -1),Replacer.ReplaceValue,{"dateTo"}), addMonthList = Table.AddColumn(#"replaceDateTo-1Day", "monthList", each List.Transform( {Number.From([Date])..Number.From([dateTo])}, each Date.StartOfMonth(Date.From(_)) )), expandMonthList = Table.ExpandListColumn(addMonthList, "monthList"), tableDistinct = Table.Distinct(expandMonthList), remOthCols = Table.SelectColumns(tableDistinct,{"monthList", "Sales"}) in remOthColsThis gives a monthly target table:
Pete
7 Replies
Hi maati1980 ,
Here's a way of increasing the granularity of your target table to match your monthly sales table. I've assumed that the targets set quarterly are the monthly targets for the next 3 months:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDWMzIwMlLSUTJSitWBCJnBhEzhQpYwIUMDmJihEVzMWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}), sortASC = Table.Sort(chgTypes,{{"Date", Order.Ascending}}), addIndex1 = Table.AddIndexColumn(sortASC, "Index1", 1, 1, Int64.Type), addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type), mergeOnSelf = Table.NestedJoin(addIndex0, {"Index1"}, addIndex0, {"Index0"}, "addIndex0", JoinKind.LeftOuter), expandDate = Table.ExpandTableColumn(mergeOnSelf, "addIndex0", {"Date"}, {"dateTo"}), replaceNullDateTo = Table.ReplaceValue(expandDate,null, each Date.AddMonths([Date], 3) ,Replacer.ReplaceValue,{"dateTo"}), #"replaceDateTo-1Day" = Table.ReplaceValue(replaceNullDateTo,each [dateTo], each Date.AddDays([dateTo], -1),Replacer.ReplaceValue,{"dateTo"}), addMonthList = Table.AddColumn(#"replaceDateTo-1Day", "monthList", each List.Transform( {Number.From([Date])..Number.From([dateTo])}, each Date.StartOfMonth(Date.From(_)) )), expandMonthList = Table.ExpandListColumn(addMonthList, "monthList"), tableDistinct = Table.Distinct(expandMonthList), remOthCols = Table.SelectColumns(tableDistinct,{"monthList", "Sales"}) in remOthColsThis gives a monthly target table:
Pete
Thanks for your direction. It's clear. But what if I would add a column to both tables with customer. Then the index would not work as the dates would be multiplied by different customers.
