cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
walkra Member
Member

Adding rows in Power Query (M)

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.

 

Missing Records.png

 

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!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Adding rows in Power Query (M)

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))

Capture.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Adding rows in Power Query (M)

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))

Capture.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

walkra Member
Member

Re: Adding rows in Power Query (M)

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!!!

Community Support Team
Community Support Team

Re: Adding rows in Power Query (M)

Hi @walkra ,

 

Please download the pbix here as the picture below.

 

Capture.PNG

 


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. 🙂

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
walkra Member
Member

Re: Adding rows in Power Query (M)

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors