Export to Excel Help- Excel file data issues

[SamanthaLavoie]

Hello, 

I have a gallery with options to sort the gallery data. The data comes from a collection made from a sharepoint list. After the data is sorted I have made a button to export the sorted data to excel. When I click the Export to Excel button, it generates an excel file in a specified folder (using flow) but there are two problem with the exported data. 

1) All columns that are not assigned as single lines of text in my SharePoint list, the cell values shown in the exported excel file are shown as {"Value":"JUN 23"} instead of JUN 23. This makes the file very difficult to read and use.

2) It exports all of the data from the sharepoint list instead of just the sorted data that shows in the gallery. I only want the sorted data to be exported.

How can I fix these issues in order to get usable data in the excel file that is exported? I have included screen shots of my flow and what the excel file data looks like in my exported file. The code I'm using on the Export to Excel button On Select property is:

ClearCollect(colContractData, ShowColumns('Main Data 5-24', "Title", "field_1", "DIVISIONASSIGNMENT","field_10","ONMCR_x003f_","CMName","COSSPECIALIST1","COSLEAD1", "field_15","field_24","field_25"));

'ExportToExcel'.Run(JSON(colContractData,JSONFormat.IncludeBinaryData & JSONFormat.IgnoreUnsupportedTypes));

Clear(colContractData);

Collect(colContractData,'Main Data 5-24');

I would really, really, appreciate help fixing this issue. Thank you. 

This is what my exported file data looks like.

Here's my flow:

   

[WarrenBelz]

Hi @SamanthaLavoie ,

You need to use AddColumns to convert the Choice/Lookup columns ti Text and then ShowColumns to send only the Text values including the ones created.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[SamanthaLavoie]

Hi Warren, 

Thanks for your response and sorry for the delay in my response back. Can you show me how to do what you're recommending? I don't know how to nest AddColumns and ShowColumns together, and make it work, and I can't find an example online. 

This is what I have so far on the OnSelect property of the Export to Excel Button.

ClearCollect(colContractData,

AddColumns('Sharepoint Data Source', "New Column Name1", "Old Column Name1", "New Column Name2", "Old Column Name2","New Column Name3", "Old Column Name3", "New Column Name4", "Old Column Name4", "New Column Name5", "Old Column Name5", "New Column Name6", "Old Column Name6", "New Column Name7", "Old Column Name7", "New Column Name8", "Old Column Name8"),

ShowColumns('Sharepoint Data Source', "New Column Name1", "New Column Name2","New Column Name3","New Column Name4","New Column Name5","New Column Name6","New Column Name7", "New Column Name8", "Column9 Name", "Column 10 Name", "Column11 Name");

'ExportToExcel'.Run(JSON(colContractData,JSONFormat.IncludeBinaryData & JSONFormat.IgnoreUnsupportedTypes));

Clear(colContractData);

Collect(colContractData,'Main Data 5-24');

If you could help me get my code into the correct format, you'd make my last two weeks! 

Thank you for your help. 🙂

[WarrenBelz]

Hi @SamanthaLavoie ,

You can put the rest in (I have done 3) - also you do not need the collection hanging around

With(
   {
      _Data:
      ShowColumns(
         AddColumns(
            'Main Data 5-24', 
            "New Column Name1", 
            'Old Column Name1'.Value, 
            "New Column Name2", 
            'Old Column Name2'.Value,
            "New Column Name3", 
            'Old Column Name3'.Value
         ),
         "New Column Name1", 
         "New Column Name2",
         "New Column Name3"
      )
   },
   'ExportToExcel'.Run(
      JSON(
         _Data,
         JSONFormat.IncludeBinaryData & 
         JSONFormat.IgnoreUnsupportedTypes
      )
   )
);

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[SamanthaLavoie]

Thank you for providing the code structure! Unfortunately, the only columns that are showing in my exported excel file now are the 3 columns that are shown in blue in the below code.

Also, shown in the exported Excel file are all of the records in my sharepoint file, not just the filtered data in my gallery. I have several gallery filters for the user to use to find the specific data they want to export into this excel report. What needs to be added to make that happen?

I'm not sure if it matters or not but I wanted to let you know that some of the columns that are not showing up are lookup columns. 

The code I'm using now is: 

With(
{
_Data:
ShowColumns(
AddColumns(
'Main Data 5-24',
"CONTRACT MONITOR NAME",
'CMName'.Value,
"COS LEAD NAME",
'COSLEAD1'.Value,
"COS SPECIALIST NAME",
'COSSPECIALIST1'.Value,
"DPS DIVISION",
'DIVISIONASSIGNMENT'.Value,
"CONTRACT ON MCR?",
'ONMCR_x003f_'.Value,
"CONTRACT RISK LEVEL",
'field_10'.Value,
"COMPLIANCE STATUS",
'field_24'.Value,
"NEXT CMR DUE DATE",
'field_25'.Value
),
"Title",
"field_1",
"field_15"
)
},
'ExportToExcel'.Run(
JSON(
_Data,
JSONFormat.IncludeBinaryData &
JSONFormat.IgnoreUnsupportedTypes
)
)
);

Thank you for your help with this. I really appreciate it!

Samantha

[WarrenBelz]

Hi @SamanthaLavoie ,

That is because you need to list all the columns to be exported in your ShowColumns list.

[SamanthaLavoie]

Hi Warren,

I should have thought about needing to include all of the columns that I wanted to show. Making that change has fixed that problem and now all of the columns I want shown are shown. But, it's showing all 250 records instead of the 3 records shown in my gallery after the filters were applied. How can I make it only show the records shown in the gallery after the filters are applied?

With(
{
_Data:
ShowColumns(
AddColumns(
'Main Data 5-24',
"CONTRACT MONITOR NAME",
'CONTRACT MONITOR'.Value,
"COS LEAD NAME",
'COS LEAD'.Value,
"COS SPECIALIST NAME",
'COS SPECIALIST'.Value,
"DPS DIVISION",
DIVISION.Value,
"CONTRACT ON MCR?",
'ON MCR?'.Value,
"CONTRACT RISK LEVEL",
'RISK LEVEL'.Value,
"COMPLIANCE STATUS",
'CURRENT COMPLIANCE STATUS'.Value,
"NEXT CMR DUE DATE",
'NEXT REPORT DUE DATE'.Value
),
"Title",
"field_1",
"field_15",
"CONTRACT MONITOR NAME",
"COS LEAD NAME",
"COS SPECIALIST NAME",
"DPS DIVISION",
"CONTRACT ON MCR?",
"CONTRACT RISK LEVEL",
"COMPLIANCE STATUS",
"NEXT CMR DUE DATE"
)
},
'ExportToExcel'.Run(
JSON(
_Data,
JSONFormat.IncludeBinaryData &
JSONFormat.IgnoreUnsupportedTypes
)
)
);

Thanks again for your help with this. I really appreciate it.

Samantha

[WarrenBelz · Accepted solution]

@SamanthaLavoie ,

That is because you are sending the whole list (there is no reference to a Gallery or filters) - it is best to apply the same filter as you have on the gallery. 

With(
   {
      _Data:
      ShowColumns(
         AddColumns(
            Filter(
               'Main Data 5-24',
               YourFilterHere
            ),
            "CONTRACT MONITOR NAME",
            'CONTRACT MONITOR'.Value,
            "COS LEAD NAME",
            'COS LEAD'.Value,
            "COS SPECIALIST NAME",
            'COS SPECIALIST'.Value,
            "DPS DIVISION",
            DIVISION.Value,
            "CONTRACT ON MCR?",
            'ON MCR?'.Value,
            "CONTRACT RISK LEVEL",
            'RISK LEVEL'.Value,
            "COMPLIANCE STATUS",
            'CURRENT COMPLIANCE STATUS'.Value,
            "NEXT CMR DUE DATE",
            'NEXT REPORT DUE DATE'.Value
         ),
         "Title",
         "field_1",
         "field_15",
         "CONTRACT MONITOR NAME",
         "COS LEAD NAME",
         "COS SPECIALIST NAME",
         "DPS DIVISION",
         "CONTRACT ON MCR?",
         "CONTRACT RISK LEVEL",
         "COMPLIANCE STATUS",
         "NEXT CMR DUE DATE"
      )
   },
   'ExportToExcel'.Run(
      JSON(
         _Data,
         JSONFormat.IncludeBinaryData &
         JSONFormat.IgnoreUnsupportedTypes
      )
   )
);

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[SamanthaLavoie]

It worked! Yay!!!! It worked!!! 🙂

Thank you so much, Warren! And thank you for your patience, I'm excited to learn and will use this code model to create several other export-to-Excel button features in my app. Have a great day!

Samantha