Adding rows in Power Query (M)

[walkra]

Hello how are you?. I have found in the forum a solution for part of a problem that has arisen.

https://community.powerbi.com/t5/Desktop/adding-rows-in-power-query-and-performance-issue/m-p/339558...

I was trying to modify that solution but I can not find a way to do it.
It happens that I have a list of records where there are missing records to complete, which have to take the value of the next record (not the previous one) and in the range of the same Material and Center. I attach the example and how it should look.

I have written this code which is very similar to the solution I had found, but I do not know how to do so that it only compare within the range of Material and Center, and that in the lost record goes the Amount of the next record:

let
Origen = Excel.Workbook(File.Contents("C:\Users\walter.krajcar\Documents\Libro1ssss.xlsx"), null, true),
ConMes_Sheet = Origen{[Item="ConMes",Kind="Sheet"]}[Data],
#"Encabezados promovidos" = Table.PromoteHeaders(ConMes_Sheet, [PromoteAllScalars=true]),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"Date", type date}, {"Mes", Int64.Type}, {"Amount", Int64.Type}}),
#"Índice agregado" = Table.AddIndexColumn(#"Tipo cambiado", "Índice", 1, 1),
#"Added Custom" = Table.AddColumn(#"Índice agregado", "Custom", each try #"Índice agregado"[Mes]{[Índice]}-[Mes] otherwise 1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each List.Dates(Date.From([Date]),[Custom],#duration(31,0,0,0))),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1")
in
#"Expanded Custom.1"

I'm sorry my English!. 

Thank You Very Much!!

[v-frfei-msft · Accepted solution]

Hi @walkra ,

I can do this by DAX, Please check the following formulas as below.

Table 2 = FILTER(CALENDARAUTO(),DAY([Date])=1)

Table 3 = CROSSJOIN('Table','Table 2')

Table 4 = SUMMARIZE('Table 3','Table 3'[Date1],'Table 3'[Centro],'Table 3'[Material])

Then in table 4, we can create some calculated columns to work on.

Column = var c = 'Table 4'[Centro]
var b = 'Table 4'[Material] 
var a = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Centro]=c && 'Table'[Material] = b))
var bd  = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Centro]=c && 'Table'[Material] = b))

return
IF([Date1]>=bd && [Date1]<=a,1,0)

Column 2 = LOOKUPVALUE('Table'[Amount],'Table'[Centro],[Centro],'Table'[Date],[Date1],'Table'[Material],[Material])

Column 4 =
VAR c = 'Table 4'[Centro]
VAR m = 'Table 4'[Material]
VAR d = 'Table 4'[Date1]
VAR mind =
    CALCULATE (
        MIN ( 'Table 4'[Date1] ),
        FILTER (
            'Table 4',
            'Table 4'[Centro] = EARLIER ( 'Table 4'[Centro] )
                && 'Table 4'[Material] = EARLIER ( 'Table 4'[Material] )
                && 'Table 4'[Date1] > d
                && 'Table 4'[Column 2] <> BLANK ()
        )
    )
RETURN
    IF (
        'Table 4'[Column 2] = BLANK (),
        CALCULATE (
            SUM ( 'Table 4'[Column 2] ),
            FILTER (
                'Table 4',
                'Table 4'[Centro] = EARLIER ( 'Table 4'[Centro] )
                    && 'Table 4'[Material] = EARLIER ( 'Table 4'[Material] )
                    && 'Table 4'[Date1] = mind
            )
        ),
        'Table 4'[Column 2]
    )

After that, we can get the result table.

Result = CALCULATETABLE('Table 4',FILTER('Table 4','Table 4'[Column]<>0))

[walkra]

Uuauuu. Excellent!!!

I could replicate it with the same example on my laptop (I didn´t see your file .pbix attached; now I'm going to replicate it with the real data).
There are many things that I am going to have to study because they are totally new! Very didactic!!.
Only one question in: Table 2 = FILTER (CALENDARAUTO (), DAY ([Date]) = 1), where does [Date] come from? (and what I understand is that by equating to "1" brings the first day of each month, no?).
(I would have many more questions but I should investigate on my side).
Many Thanks!!!

[v-frfei-msft]

Hi @walkra ,

Please download the pbix here as the picture below.

Only one question in: Table 2 = FILTER (CALENDARAUTO (), DAY ([Date]) = 1), where does [Date] come from? (and what I understand is that by equating to "1" brings the first day of each month, no?).
(I would have many more questions but I should investigate on my side).

---

The [Date] is in the CALENDARAUTO (). Here I make the filter day is 1 to only get the first day of each month as you said. If any other question, feel free to let me know please. 🙂

[walkra]

v-frfei-msft Thank you, very much!!