start | end | Custom | |
18-10 | 18-25 | 10 | |
11 | |||
12 | |||
13 | |||
14 | |||
15 | |||
16 | |||
17 | |||
18 | |||
19 | |||
20 | |||
21 | |||
22 | |||
23 | |||
24 | |||
25 | |||
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Text = Table.TransformColumnTypes(Source,{{"start", type text}, {"end", type text}}),
ETAD = Table.TransformColumns(Text, {{"start", each Text.AfterDelimiter(_, "-"), type text}}),
ETAD2 = Table.TransformColumns(ETAD, {{"end", each Text.AfterDelimiter(_, "-"), type text}}),
Number = Table.TransformColumnTypes(ETAD2,{{"start", Int64.Type}, {"end", Int64.Type}}),
List = Table.AddColumn(Number, "Custom", each {[start]..[end]}),
Serie = Table.ExpandListColumn(List, "Custom"),
TSC = Table.SelectColumns(Serie,{"Custom"})
in
TSC
Number | Date | Value | Date | ||
18-200 | 18-220 | 09/01/2019 | |||
18-201 | 25/11/2018 | ||||
18-202 | 26/11/2018 | ||||
18-203 | 27/11/2018 | ||||
18-204 | 28/11/2018 | ||||
18-205 | 29/11/2018 | ||||
18-206 | 30/11/2018 | ||||
18-207 | 01/12/2018 | ||||
18-208 | 02/12/2018 | ||||
18-209 | |||||
18-210 | |||||
18-211 | |||||
18-212 | 01/01/2019 | ||||
18-213 | 02/01/2019 | ||||
18-214 | 03/01/2019 | ||||
18-215 | 04/01/2019 | ||||
18-216 | 05/01/2019 | ||||
18-217 | 06/01/2019 | ||||
18-218 | 07/01/2019 | ||||
18-219 | 08/01/2019 | ||||
18-220 | 09/01/2019 | ||||
18-221 | 10/01/2019 | ||||
18-222 | 11/01/2019 | ||||
18-223 | 12/01/2019 | ||||
18-224 | 13/01/2019 | ||||
18-225 | 14/01/2019 | ||||
18-226 | 15/01/2019 | ||||
18-227 | 16/01/2019 | ||||
18-228 | 17/01/2019 | ||||
18-229 | |||||
18-230 | |||||
18-231 | |||||
18-232 | |||||
18-233 | |||||
18-234 | |||||
18-235 | |||||
18-236 | |||||
18-237 | |||||
18-238 | |||||
18-239 | |||||
18-240 | |||||
18-241 | |||||
18-242 | 22/11/2019 | ||||
18-243 | 23/11/2019 | ||||
18-244 | 24/11/2019 | ||||
18-245 | 25/11/2019 | ||||
18-246 | 26/11/2019 | ||||
18-247 | 27/11/2019 | ||||
18-248 | 28/11/2019 | ||||
18-249 | 29/11/2019 | ||||
18-250 | 30/11/2019 | ||||
let
Src1 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Src2 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
Join = Table.NestedJoin(Src2,{"Value"},Src1,{"Number"},"Src1",JoinKind.LeftOuter),
Exp = Table.ExpandTableColumn(Join, "Src1", {"Date"}, {"Date"}),
TDate = Table.TransformColumnTypes(Exp,{{"Date", type date}}),
TSC = Table.SelectColumns(TDate,{"Date"})
in
TSC