I start month with 5 columns but in the end of the month one column does not exist in the rowset.

[Anonymous]

I have a PBI report which data source is MS Lists.

It is about auditing some criterias.

In the beggining of the month I have 5 columns ( PASSED, FAILED, INCONCLUSIVE, NOT APPLICABLE, NOT CHECKED).

But in the end of the month the column NOT CHECKED no longer exists as all audits have been checked.

Because of that I get a refresh error in PBI Service:

Data source error: The '<pii>NOT CHECKED</pii>' column does not exist in the rowset. Table: AuditsAll.

How can I avoid this?

It is my DAX formula:

RESULT =
IF (
    AuditsAll[PASSED] + AuditsAll[FAILED] + AuditsAll[INCONCLUSIVE] + AuditsAll[NOT APPLICABLE] = 0,
    "NOT AUDITED",
    IF (
        AuditsAll[FAILED] > 0,
        "FAILED",
        IF (
            AuditsAll[INCONCLUSIVE] > 0,
            "INCONCLUSIVE",
            IF ( AuditsAll[NOT CHECKED] > 0, "IN PROGRESS", "PASSED" )
        )
    )
)

[v-yanjiang-msft]

Hi @Anonymous ,

I'm not very clear how your data source operates. When the NOT CHECKED column has no value, could you add a space " " or "null"?

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbriga]

Try unpivoting the columns PASSED, FAILED, INCONCLUSIVE, NOT APPLICABLE, NOT CHECKED in Power Query.

Using = Table.UnpivotOtherColumns, should the NOT CHECKED column go missing, it won't cause a query error.

We'll call the number of audits for each "Value" and the field value (PASSED, FAILED,...) "Status" for now.

Now,

RESULT =
IF (
    CALCULATE(
            SUM(AuditsAll[Value]),
             KEEPFILTERS(AuditsAll[Status] <> "NOT CHECKED")
             )= 0,
    "NOT AUDITED",
    IF (
        CALCULATE(
            SUM(AuditsAll[Value]),
             KEEPFILTERS(AuditsAll[Status] = "FAILED")
             ) > 0,
        "FAILED",
        IF (
            CALCULATE(
            SUM(AuditsAll[Value]),
             KEEPFILTERS(AuditsAll[Status] = "INCONCLUSIVE")
             ) > 0,
            "INCONCLUSIVE",
            IF ( CALCULATE(
            SUM(AuditsAll[Value]),
             KEEPFILTERS(AuditsAll[Status] = "NOT CHECKED")
             ) > 0, "IN PROGRESS", "PASSED" )
        )
    )
)

Because "Not checked" is just another possible value of [Status], nothing would occour if it's missing.