Dulpicate date rows between Earliest and Latest date in a table

[The8]

Hello ,

I have a table Data as below in Power Query editor.

Material     Date           Text     Place     Quantity.   Price
     X1     01.01.2023   AADD     Top        88000    130262
     X1     01.01.2024   AADD     Top        66000     99220
     X1     01.02.2023   AADD     Top        110000   154022
     X1     01.02.2024   AADD     Top        88000     127028
     X1     01.09.2024   AADD     Top        3164       17402
     X2     01.01.2023   ABAB      Top        66000     133643,4
     X2     01.01.2024   ABAB      Top        36000      73198,8
     X2     01.02.2023   ABAB      Top        66000      125947,8
     X2     01.02.2024   ABAB      Top        56660      111166,92
     X2     01.12.2024   ABAB      Top        1050        5103
     X3     01.01.2023  QWER      Top        84000      283080
     X3     01.01.2024  QWER      Top        0              0
     X3     01.02.2023  QWER      Top        0              0
     X3     01.02.2024  QWER      Top        42000      128520
     X3     01.11.2024  QWER      Top        1578        6458
     X4     14.02.2023  RTZE        Top         3159       16790,1
     X4     01.03.2024  RTZE        Top         6300       30863,7

For every material in above data I want to add duplicate rows for each material with every first date of the month between earliest and latest dates with Text and Place columns being same for particular material but Quantity and Price columns should be zero for these duplicated rows.

For example, as in above data, for material X4 I have data with earliest date on 14..02.2023 and lastest date 01.03.2024 but I don't have any data in between these dates for material X4, but I want to add/duplicate data for all the dates between earliest and latest dates with Text and Place columns being same with Quantity and Price columns should be 0.

How can I do it in only in Power BI power query editor and all the data should be in One table only ?

I tried using Merge Option by creating new table but I can only do it for only one in material of my data as below for example but I want it for all materials the same way...

Any suggestions?

Thank you !!

[Ashish_Mathur]

Hi,

I am not cleat about the expected result.  Show the expected result for X3 and X4.

[lbendlin · Accepted solution]

Not exactly sure why you would need to torture your data like that, but here goes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJda8IwFAbgvxJ6HeR85POyUi8HU4SNSS+KeFGQbUgH279fWk0tmARDoUkbHt5zksOheumG06XvzmIcTViI+9iffodp8nrujuOP7U/3OfTD32r8dumPp6qVh2ra8o7TC3AVHgLisKjrprlCX9/RdA4AxgkykKE8oDKAMTdAeE8ESYBKCRDhKqBWQOkEVEowlyCQLJBLCr4kMBoV09gQYiFQoo3rei3ybUBmo1iqPKJyCM+IsIzeSZdE6MkkoR/aK1tSslG0CU48IERjpE+2BcsMgoaZROAFwQ+d3b5tdg+CU3M55Bgc5AmVIeYEt2VSoFKI54VcBkWLU3GakgQWy0BtXZwbpZener29qO5l7PYfm7h5YYSrrn30jPUg8YEZa+FbkBxjOFYjwpkYlrZq238=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Positions" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 12,  24,  34,  45,54}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Positions",{{"Column1.1", Text.Trim, type text}, {"Column1.2", Text.Trim, type text}, {"Column1.3", Text.Trim, type text}, {"Column1.4", Text.Trim, type text}, {"Column1.5", Text.Trim, type text}, {"Column1.6", Text.Trim, type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}},"de"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Text", "Place"}, {{"Date", each {Int64.From(List.Min([Date]))..Int64.From(List.Max([Date]))}, type list}}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Grouped Rows", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.Day([Date])=1),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Material", "Text", "Place", "Date"}, #"Changed Type", {"Material", "Text", "Place", "Date"}, "Filtered Rows", JoinKind.FullOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Material", "Date", "Text", "Place", "Quantity.", "Price"}, {"Material.1", "Date.1", "Text.1", "Place.1", "Quantity.", "Price"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Filtered Rows",null,each [Material.1],Replacer.ReplaceValue,{"Material"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,each [Text.1],Replacer.ReplaceValue,{"Text"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,each [Place.1],Replacer.ReplaceValue,{"Place"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,each [Date.1],Replacer.ReplaceValue,{"Date"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value3",{"Material", "Text", "Place", "Date", "Quantity.", "Price"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Removed Other Columns",null,"0",Replacer.ReplaceValue,{"Quantity.","Price"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value4",{ {"Date", type date}, {"Place", type text}, {"Text", type text}, {"Material", type text}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"Quantity.", type number}, {"Price", type number}},"nl")
in
    #"Changed Type3"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.