Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhirajkakani
Frequent Visitor

How to add rows to a new custom table based on values in other table?

Hello,

 

I'm stuck in one of the business use case where we need to add duplicate entries in a new custom table from the inputs given in other table.

 

e.q. consider i have a table with State, District, Date and #Months as the columns

 

StateRegion#MonthDate
TNHYD301-Oct-16

 

 

Now we want to dynamically create a custom table which will have duplicate entries with the same State, Region but the date being pushed to the next month based on the #Month Column

 

Expected Output is 

StateRegionMonthDate
TNHYD301-Nov-16
TNHYD301-Dec-16
TNHYD301-Jan-17

 

As the #Month is 3 the 3 rows need to be pushed to the next three months.

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @abhirajkakani

 

The thread to ahieve your requirement is: new calendar table, cross join tables and filter the joined table.

 

You can take the below formulas as a reference:

CalendarTable =
CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2017, 12, 31 ) )

FilterCalendar =
CALCULATETABLE (
    CalendarTable,
    FILTER ( CalendarTable, CalendarTable[Date].[Day] = 1 )
)

CrossTable =
ADDCOLUMNS (
    CROSSJOIN ( AddRows, FilterCalendar ),
    "DateDiff", IF (
        AddRows[Date] > FilterCalendar[Calendardate],
        DATEDIFF ( FilterCalendar[Calendardate], AddRows[Date], MONTH ),
        DATEDIFF ( AddRows[Date], FilterCalendar[Calendardate], MONTH )
    )
)

FilterCrossTable =
SELECTCOLUMNS (
    CALCULATETABLE (
        CrossTable,
        FILTER (
            CrossTable,
            CrossTable[Date] < CrossTable[Calendardate]
                && CrossTable[DateDiff] <= CrossTable[Month]
        )
    ),
    "State", CrossTable[State],
    "Region", CrossTable[Region],
    "Month", CrossTable[Month],
    "Date", CrossTable[Calendardate]
)

Best regards,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-yulgu-msft
Employee
Employee

Hi @abhirajkakani

 

The thread to ahieve your requirement is: new calendar table, cross join tables and filter the joined table.

 

You can take the below formulas as a reference:

CalendarTable =
CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2017, 12, 31 ) )

FilterCalendar =
CALCULATETABLE (
    CalendarTable,
    FILTER ( CalendarTable, CalendarTable[Date].[Day] = 1 )
)

CrossTable =
ADDCOLUMNS (
    CROSSJOIN ( AddRows, FilterCalendar ),
    "DateDiff", IF (
        AddRows[Date] > FilterCalendar[Calendardate],
        DATEDIFF ( FilterCalendar[Calendardate], AddRows[Date], MONTH ),
        DATEDIFF ( AddRows[Date], FilterCalendar[Calendardate], MONTH )
    )
)

FilterCrossTable =
SELECTCOLUMNS (
    CALCULATETABLE (
        CrossTable,
        FILTER (
            CrossTable,
            CrossTable[Date] < CrossTable[Calendardate]
                && CrossTable[DateDiff] <= CrossTable[Month]
        )
    ),
    "State", CrossTable[State],
    "Region", CrossTable[Region],
    "Month", CrossTable[Month],
    "Date", CrossTable[Calendardate]
)

Best regards,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello Guys,

 

I want to add a new calculated row everyday automatically based on the data available in tables Mack VORS and Volvo VORS. All the columns in both VORS table has column which has values 0-1,2-7,8-14,>14 and I want to calculate it everyday automatically and add the calculated value in the row with date of the day as date value. Is this possible?

 

Thanks!

 

Aging.PNGVOR.PNG

Failed to resolve name 'Addrows". It is not a valid table, variable or function name. @Yuliana GuCapture.PNG

Addrows is not a valid table, variable, or function name @Yuliana Gu

Hello Yuliana,

I have the same issue in my query.
I have a column which gives me a date and then another column which gives me how many days should I consider after that date.
I need to use this second column to create new rows putting each following date below the original date that the first column gave me. So, the output must be that in the date column, every date should be inputted.


I saw your solution to this post's question, but I'm not sure where should I put these formulas you described. Is it in the Advanced Query Editor? I've tried here, unsuccessfully.

 

Can you help me?

 

Thanks!

Hi @thiagozaiden,

 

The formulas above are used to create calculate table. One formula stands for one table. 

5.PNG

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-yulgu-msft,

 

I keep getting this error when trying the formulas:

Power BI Error.JPG

 

Any ideia how to solve this?

 

Thanks!

Hi @thiagozaiden,

 

Do you directly copy the formula in my original post and then paste it into the box? Still have this error?

 

Thanks,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-yulgu-msft. You are a savior!

Dan80
Helper II
Helper II

Hi, in power query, duplicate the original table 3 times. Then add custom column using Date.AddMonths formula for 1, 2 and 3 months in the 3 duplicated tables, then delete the original date column. Then append the 4 tables together. Hope it works. Dan

Thanks Dan80 for the quick reply but the number of months can be any number i.e. it can also be greater than 100 in some cases. 

 

Hence, creating different tables is not a solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.