What file type do you use to export large data set from Alteryx to PowerBI?

[vball]

I am currently using Excel but Excel does not like huge datasets (over 250K rows) and has error message. (The error message does not seem to cause the data to be inaccurate. It is just annoying.)

I also tried csv and json but both file types have numerous error messages in Alteryx, which seems to be because the dataset I have include a lot of string characters like comma. Therefore, Excel is better than those two.

Is there another file type you'd recommend?

I don't plan to use the PowerBI Rest API as the documentation on it said it has a lot of limitations like maximum rows and performance time. 

Thank you.

[gautiergodard]

Hello @vball 

I would recommend trying to output your results to SQLite file.

**Please like this post and mark as resolved if it answered your question!**

[Robin_McIntosh]

@vball - What is the error message you're receiving with Excel?  Is it an error within Alteryx or within Excel/Power BI?  Can you not export to just an Excel file and then bring that into Power BI?

[Felipe_Ribeir0]

Hi @vball 

This error message is not related to the output file choices (250k rows is a little heavy for excel, but it can handle it), can you share a little more about what errors are you getting?

Anyway, here is the list of better options for this:

1)Database (If you have and can use it, it is the best option for multiple reasons).

2)Power BI Output tool (If you have power BI Service, you can update the dataset there with this tool: Power BI Output - Alteryx Community).

3)For files, .csv is the best option. It can handle any amount of rows and it is the fastest file option to load from PBI (take a look at this test: Best file type for Power BI | 5X FASTER REFRESH! - YouTube)

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[vball]

Thank you for the advice.

I don't have a database that I can use, and the data needs to be used by co-workers for manual testing so database will be tough. 

Does PowerBI Output Tool have restrictions on rows? I saw the restriction warning on both Alteryx and PowerBI Push Dataset webpages. 

I agree with .csv. The only problem for my particular file is that it has many fields with "," in it and therefore .csv cannot be used properly. I cannot even change the delimiter because many fields also have other symbols like ".". But if .csv can still work by some specific setup, can you advice? I can give that a try.

[vball]

That is exactly what I am trying to do: export to Excel and bring to PowerBI. I suppose there is no error message if I directly load the file from excel to PowerBI as long as I don't open the excel file. But knowing that when opening the excel file using excel has this error message just makes me nervous. 

The error message shows up in Excel and says the file needs to be repaired. When it is repaired, it says Removed Records: Formula from /xl/worksheets/sheet1.xml part.

At first, I thought this is due to formula created in Alteryx. But I tried to import an excel file to Alteryx and export it right away back to excel. The same error message pops up. The weird thing is that the original excel file has no error message when opening up in Excel. It seems that as soon as it gets "showered" in Alteryx, an error will pop up. This tells me that it might not be an issue of Excel unable to handling the size of the data set.

In addition, if I export a smaller file to excel from Alteryx, there is no error message when opening up in Excel. If I created formula in Alteryx > export a file to excel and the file is small, there is no error message when opening up in Excel. 

The whole thing does not make sense to me at all.

[vball]

Thank you but we don't have SQLite database. Do you know if that file can be directly imported to PowerBI?

[Felipe_Ribeir0]

Hi @vball 

You can use the .csv output with ; as delimiter, so you would not have problems with ,. it is a good choice.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[Felipe_Ribeir0]

Hi @vball 

It worked?

[vball]

Hi @Felipe_Ribeir0 

I was able to try .csv and for whatever reason, this time there is no significant error even if I export without changing the delimiter. That is really good news. However, I do have two follow-up questions (sorry for so many questions)

1) The .csv file double the size of .xlsx file with same data. Is that expected?

2) The error when exporting using .csv is "Newlines detected in data, output file might not be readable by other tools". It's just a warning, not actually an error. I have not tried to input the data to PowerBI to see if it is indeed not readable. But do you know why this error shows up?

Another thing I want to mention is that my data has non-English characters and Alteryx community posts on handling non-English characters said I should not use .csv to export the data as .csv does not handle non-English characters well. So I might not be able to use .csv after all. Nevertheless, thank you for providing a possibility. (Sorry I cannot accept your answer as a solution now as that will mark the post as solved. I will for sure accept your answer as solution after getting all my questions answered. Thank you!)