Forum Discussion

anandav's avatar
anandav
Skilled Sharer
8 years ago
Solved

M / Power Query help for table merge (Conditional Cross Join)

Hi M experts,   I have a Tasks table with following data: Task Name Start End A 01/08/2018 03/08/2018 B 07/08/2018 17/08/2018 C 21/08/2018 31/08/2018   I have a Calendar t...
Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    Sure: you add a column with list of those dates like so:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dew0DcyMLQAcYzhnFidaCUnkJA5kryhOYq8M1DICFm/MYITGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Task Name" = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task Name", type text}, {"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From([Start])..Number.From([End])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"