Dealing with blanks in PowerBI Integration

[aaa12]

Hello - I'm imbedding a PowerApp in PowerBI. The connection is established. 

When the Submit button is pressed, a flow (CSVAttachement) should trigger, however, in adding logic to pass data from PowerBI, I'm facing an error (JSON parsing error, expected 'number' but got 'string'). The issue is due to PowerBI sending NULL or Blank values in some of the fields.

I know I have to complete a combination of creating a collection, using if then statements and pass using the JSON command however I can't find the right systax. It has been several days now and I am turing to the Community for help.

My code for the submit button:

Collect(

    check_null,

     ShowColumns([@PowerBIIntegration].Data, Environment,'Created at'),

    {'Created at': If(

 IsBlankOrError('Created at'.Text),

 0,

 Value('Created at'.Text)

    ),

 Environment: If(

    IsBlankOrError(Enviornment.Text),

    0,

    Value(Environment.Text)

 )

 }

);

 CSVAttachement.Run(

        JSON(

            check_null,

            JSONFormat.IncludeBinaryData

        ), Dropdown1.SelectedText,

        Comments.Text

    )

[Anonymous]

Thanks for the reply from Jonvoge and SaiTejaTalasila.

Hi @aaa12  , 

Have you solved your problem? If so, please share your solution and accept it as solution or mark the helpful replies, it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[aaa12]

Unfortuantely, neither responses helped resolved the issue. I remvoed columns which caused problems and will revisit them later on.

[Anonymous]

Hi @aaa12 ,

You can use the IsNumeric function to determine whether 'Created at'.Text is a number:

IsNumeric function - Power Platform | Microsoft Learn

If it is a number, you can output it directly; if it is not a number, you can use the Value function to convert it.

Regarding the issue of unrecognized columns in the IsBlankOrError function, please make sure the column names are correct and refer to the following documentation for the syntax to modify:

Error, IfError, IsError, and IsBlankOrError functions - Power Platform | Microsoft Learn

You need to have a good understanding of the syntax of the corresponding function in Power Apps before practicing it.

Best Regards,
Zhu
Community Support Team

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

[Jonvoge]

If your Check_Null collection looks correct, but you simply wish to remove all null values, you could create another temporary collection in between, to filter out any blank rows.

The correct syntax you may need to play around with yourself, but you could do something like:

Collect(

    check_null,

     ShowColumns([@PowerBIIntegration].Data, Environment,'Created at'),

    {'Created at': If(

 IsBlankOrError('Created at'.Text),

 0,

 Value('Created at'.Text)

    ),

 Environment: If(

    IsBlankOrError(Enviornment.Text),

    0,

    Value(Environment.Text)

 )

 }

);

ClearCollect(tempCol, Filter(check_null, !IsBlank(Environment) || !IsBlank('CreatedAt')));

 CSVAttachement.Run(

        JSON(

            tempCol,

            JSONFormat.IncludeBinaryData

        ), Dropdown1.SelectedText,

        Comments.Text

    )

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[SaiTejaTalasila]

Hi @aaa12 ,

Try this one-

• Collect(
  check_null,
  ShowColumns(
  [@PowerBIIntegration].Data,
  "Environment", 'Created at'
  ),
  {
  'Created at': If(
  IsBlankOrError('Created at'),
  0, 
  Value('Created at')
  ),
  Environment: If(
  IsBlankOrError(Environment),
  "Unknown",
  )
  }
  );

CSVAttachement.Run(
JSON(
check_null,
JSONFormat.IncludeBinaryData
),
Dropdown1.SelectedText,
Comments.Text
);

[aaa12]

@SaiTejaTalasila  Thank you for your response. I tried your solution but the column is not recognized in the isBlankError function. In the screenshot below, I'm attempting to enter "created at" but the field is not among the options to select.

Could you offer addtional guidance?