issue = Generate Series function cannot be blank.

[zishan19]

Hello 

so i have data of 3 years 1st year is ACT2022 (previos year) 2nd year is ACT2023 third year is BUD2023.  in the sheet i have data in a column called period12 which contains my month data and the months are written as 1. period for 1st month and 2. period for 2nd month and so on till 12. period. so i want to create a new column in my powerbi as months as 1, 2, 3 so on but i am not usung power query for it. i am creating it directly like how we creat measures. i got the code from the last user who made the file and he got the months column sorted. 

i applied the code and it woked if i go on power query and remove my bud2023 year but it does not work if i add the bud2023 year. but i know it should work becase last year the below code worked for the same file. there are some changes in file but there is no major change. 

i will provide the code which i am using which is is the same code used last year. 

please tell me why am i facing this issue of generateseries cannot be blank. 

please let me know if the information provided is not understandable and what more can i provide to solve the issue. 

Month =

var __string = 'RawData'[Period12]

var __stringLength = LEN( __string )

var thenumbers =

    CONCATENATEX(

        FILTER(

            ADDCOLUMNS(

                ADDCOLUMNS(

                    GENERATESERIES( 1 , __stringLength , 1 )

                    , "@char" , MID( __string , ''[Value] , 1 )

                )

                , "isnumber" , IF( ISERROR( VALUE( [@char] ) ) , 0 , 1 )

            )

            , [isnumber] = 1

        )

    , [@char]

    )

return

thenumbers

[technolog]

The code you provided is trying to extract the numeric values from the 'RawData'[Period12] column. It does this by iterating over each character in the string and checking if it's a number. If it is, it concatenates it to form the final number.

Now, the error you're seeing, "Generate Series function cannot be blank", typically arises when the GENERATESERIES function doesn't get the expected input for its start and end values. In your code, the GENERATESERIES function is using 1 as the start value and __stringLength as the end value. This means that if __stringLength is blank or zero, you'll get this error.

Given that the code works when you remove the 'BUD2023' year, it's possible that there's something about the data for that year that's causing the issue. Here are a few things to consider:

1. Check if there are any blank or NULL values in the 'RawData'[Period12] column for the 'BUD2023' year. If there are, this could be causing the __stringLength variable to be zero, which would trigger the error.

2. It's also possible that there's a non-standard character or space in the 'BUD2023' data that's affecting the LEN function's ability to correctly calculate the string length.

3. Ensure that the 'RawData'[Period12] column for 'BUD2023' is formatted consistently with the other years. If there's a change in the way the data is formatted, it could affect the way the code parses the numbers.

To troubleshoot, you might want to create a simple measure or column that just calculates the __stringLength for each row. This will allow you to quickly identify any rows where the length is zero or not what you'd expect. From there, you can investigate those specific rows to see what might be causing the issue.

Lastly, if you're still stuck, consider sharing a sample of the 'RawData'[Period12] data for the 'BUD2023' year. Sometimes, seeing the actual data can help identify issues that might not be immediately obvious from the code alone.