Export data from excel Data model into text file

[earthworm]

I have a huge huge data set . One excel workbook with multiple sheets. Each sheet containing data more then 900k rows. I have appened them into single sheet through power query into data model. Now the problem is I cannot export data back into excel due to row limits as data length is now 5 million +
I want to export this data from data model into text file . Any possibility. I tried to search the internet and there is no solution.

 

[Alex Blakenburg]

I believe this free tool from @Matt Allington will do it.

DAX Studio for Power BI: Getting Started

DAX Studio is a free tool that allows you to query your Power Pivot/Power BI data models. Learn exactly how using DAX Studio can add value ?

exceleratorbi.com.au

 

[earthworm]

I believe this free tool from @Matt Allington will do it.

DAX Studio for Power BI: Getting Started

DAX Studio is a free tool that allows you to query your Power Pivot/Power BI data models. Learn exactly how using DAX Studio can add value ?

exceleratorbi.com.au

Thanks . I tried it . But when I am trying to save it as CSV its showing below error

Error Exporting 'Query1': XML for Analysis protocol error: The 'scheme in the 'http://www.w3.org/2002/XMLSchema' namespace is expected

 

[earthworm]

Thanks . I tried it . But when I am trying to save it as CSV its showing below error

Error Exporting 'Query1': XML for Analysis protocol error: The 'scheme in the 'http://www.w3.org/2002/XMLSchema' namespace is expected

it worked finally . I just cleared the cache . Thanks for your support

 

[earthworm]

Now I am facing another issue . When I am trying to import the data the error of Bad file name or Number is appearing in access

 

[Alex Blakenburg]

Can you open the file in Notepad to see if there is any obvious issue with the file ?
Are you saying are trying to get the data into MS Access ? Why do you need to load it to PQ first ?
radacad has more options if you want to import the Date Model into Power BI first.

 

[earthworm]

Can you open the file in Notepad to see if there is any obvious issue with the file ?
Are you saying are trying to get the data into MS Access ? Why do you need to load it to PQ first ?
radacad has more options if you want to import the Date Model into Power BI first.

The file size is too huge to open in notepad . the data length is 10.4 Million
I have consolidated all the data from multiple sheets into single file through power query . And now I need to export it in text file from data model

 

[earthworm]

Another problem is that when the file is exported into csv and when I import it again in power query the data is not aligned correctly as per header. Due to various special characters present in any cell on random headers. That's has forfeit my purpose. I tried selecting comma or tab etc and still the issue exist. How can preserve the format in exact form while saving it in csv file regardless of any special character present in any cell under random headers / columns

 

[Alex Blakenburg]

Are the special characters in your original data or are they created by trying to export the data using DAX Studio ?
You didn't tell me whether you are trying to get the data in MS Access or not and if there is anything Power Query is doing that needs to be done before loading it into Access.

PS: The only reason for opening it in Notepad was to see if the file was corrupt or not since you were getting an file reading error. Your loading it using PQ covers that of. (Additional FYI I just opened a file in Notepad with $5m+ records and it had no issue with it at all, even the load time was quite short)

 

[earthworm]

Are the special characters in your original data or are they created by trying to export the data using DAX Studio ?
You didn't tell me whether you are trying to get the data in MS Access or not and if there is anything Power Query is doing that needs to be done before loading it into Access.

PS: The only reason for opening it in Notepad was to see if the file was corrupt or not since you were getting an file reading error. Your loading it using PQ covers that of. (Additional FYI I just opened a file in Notepad with $5m+ records and it had no issue with it at all, even the load time was quite short)