ERROR: "There weren't enough elements in the enumeration to complete the operation"

[Abdul2020]

Hi All ,

I know this error may have been raised previously but i couldn't find a solution that could fit the problem i am facing.

I have data that are connected to SAP BW and schedule to refresh daily but for some reason, It was working fine until April 1st started, the data refresh schedule failed. So I found out the reason for that. Basically it fails because it didn't pass one of the below steps.

What i did in that highlighted step is replaced all null with "0" then unpivot the values anything above that step works fine with no issue with the data getting refreshed.

null values for all 12 monthsreplaced all null with "0"unpivot all 12 months

However, that worked fine because this was done during March 2019 then when April 1st started, it failed and throwed the above error.  Is there away to fix this so it doesn't fail every month?

Your urgent help would be really appreciated.

Thanks

Abdul

[v-juanli-msft · Accepted solution]

Hi @Abdul2020 

You need to replace value in whole table.

You can do this by using the Table.ColumnNames function in replacement of the specific column list.  E.G. Table.ReplaceValue(#"Last Step",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Last Step"))

As tested, it can be refreshed succefully with this method.

This is my code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\4\4.30\4.30.xlsx"), null, true),
    Sheet5_Sheet = Source{[Item="Sheet5",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet5_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type date}, {"Column4", type any}, {"Column5", type date}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"name", type text}, {"10/1/2018", Int64.Type}, {"11/1/2018", type date}, {"12/1/2018", Int64.Type}, {"1/1/2019", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type1")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Best Regards
Maggie

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

[Nishantjain]

Can you confirm which step is causing the issue. Is it the "Replaced Value1" step

[Abdul2020]

Yes Step "Replaced Value1".

It works fine but it doesn't pick up any new month added to the table as part of the data refresh.

e.g April isn't showing in the data because for some reason i think it fails at this step.

The orginal data coming from BW for the date was unpivoted. So i had to pivot the date. once i do that all the null values for each months shows up then i appliy "Replace Value1" step to replace null with "0"

[Nishantjain]

I think it is because of your step "unpivot selected colums". I guess you selected all the months when you created this steps. If you did this, power query would have hard coded the months that you selected and it didn't have April at that time. This might explain why April is not showing in your data

Unstead of unpivot selected column, you unpivot other columns. This will ensure all new months are automatically picked up

[Abdul2020]

Thanks Nishantjain for your prompt repsonse.

That step where it shows "unpivot selected Col.." its not for the months its for the products type as per screenshot below those products imported from BW pivoted so i had to unpivot them and they never change they are always the same values.

But am not sure if the values there are linked to the months.. still worth a try to unpivot other col on the below as well.

I will give that a try on a sample data with the same steps but unpivot other col then add addtional month and will see if if that solves the probelm.

If you you think there is another way or perhaps a formula to fix this please hit me with it. Thanks

[Nishantjain]

Can you share a sample pbix file?

[Abdul2020]

I tried with a sample test and added April data then when refreshed the data it did show April but the step where it says replace value from null to "0" did not apply on april. as per screen shot below.

Can't attach a pbix here for some reaosn , it need be via dropbox or other external tool

[Nishantjain]

Can you attach the entire power query code from the advance editor