Power Platform Community Forum Thread Details

Hello,

I am trying to merge and create a a collection from a sharepoint list

first collection has distribution of a fund between a start and end date,

I have project status in a sharepoint list ( which has the name of the project and the status date, and financila information

In power app I am trying to create a collection which merges these 2 in another collection using following formula

ClearCollect(
    ColTest,
    AddColumns(
        g1,
        _Financial,
       If(IsBlank( LookUp('Project Status','Status Month'=Text( g1[@_Month])&&'Status Year'.Value=Text(g1[@_Year])&&ProjName=g1[@ProjectName],'Payments Current Month')),"" 
           ,Value(LookUp('Project Status','Status Month'=Text(g1[@_Month])&&'Status Year'.Value=Text( g1[@_Year])&&ProjName=g1[@ProjectName],'Total Payments')))))
 
and it generate the following collection 
I need to fill in the empty values with the last number, so if the value for 4/1/2025 and 5/1/2025 is empty, just add 3/1/2025 value=130000 so that my chart doesn't show 0 for those dates and show straight line rather
 
Thank you so much!

Categories:

Building Power Apps

I don't know if I'm helping, or just making code look pretty. Let's give a shot though:

In your If function, you ask "If 'Payments Current Month' is blank, return blank.

If(
            IsBlank(
                LookUp(
                    'Project Status',
                    'Status Month' = Text(g1[@_Month]) &&
                    'Status Year'.Value = Text(g1[@_Year]) &&
                    ProjName = g1[@ProjectName],
                    'Payments Current Month'
                )
            ),
            "",

Then, if its not blank, return , 'Total Payments'.

Value(
                LookUp(
                    'Project Status',
                    'Status Month' = Text(g1[@_Month]) &&
                    'Status Year'.Value = Text(g1[@_Year]) &&
                    ProjName = g1[@ProjectName],
                    'Total Payments'
                )
            )

If what you want is to check to see is 'Payments Current Month' is blank, the return, 'Total Payments'

If(
            IsBlank(
                LookUp(
                    'Project Status',
                    'Status Month' = Text(g1[@_Month]) &&
                    'Status Year'.Value = Text(g1[@_Year]) &&
                    ProjName = g1[@ProjectName],
                    'Payments Current Month'
                )
            ),
            Value(
                LookUp(
                    'Project Status',
                    'Status Month' = Text(g1[@_Month]) &&
                    'Status Year'.Value = Text(g1[@_Year]) &&
                    ProjName = g1[@ProjectName],
                    'Total Payments'
                )
            )

And if 'Payments Current Month' is not blank, then return 'Payments Current Month', the whole code would be:

If(
            IsBlank(
                LookUp(
                    'Project Status',
                    'Status Month' = Text(g1[@_Month]) &&
                    'Status Year'.Value = Text(g1[@_Year]) &&
                    ProjName = g1[@ProjectName],
                    'Payments Current Month'
                )
            ),
            Value(
                LookUp(
                    'Project Status',
                    'Status Month' = Text(g1[@_Month]) &&
                    'Status Year'.Value = Text(g1[@_Year]) &&
                    ProjName = g1[@ProjectName],
                    'Total Payments'
                )
            ),
            Value(
                LookUp(
                    'Project Status',
                    'Status Month' = Text(g1[@_Month]) &&
                    'Status Year'.Value = Text(g1[@_Year]) &&
                    ProjName = g1[@ProjectName],
                    'Payments Current Month'
                )
             )
        )

Below is the same code, but pretty:

ClearCollect(
    ColTest,
    With( //Let's cache the LookUp as projectStatus
        {
            projectStatus: LookUp(
                'Project Status',
                'Status Month' = Text(g1[@_Month]) &&
                'Status Year'.Value = Text(g1[@_Year]) &&
                ProjName = g1[@ProjectName]
            )
        },
        AddColumns(
            g1,
            "_Financial",
            If(
                IsBlank(projectStatus.'Payments Current Month'),
                Value(projectStatus.'Total Payments'),
                Value(projectStatus.'Payments Current Month')
            )
        )
    )
)

Let mw know if any of this worked or at least helped.