You’re offline. This is a read only version of the page.
Hello,
I have a Sharepoint list that looks like this :
I would like to create a Power Automate flow that is able to create an excel file from my lost Sharepoint looking like this:
But I have absolutely no idea how to make all the values of my "Sous-Filière" field (in my Sharepoint list) be inline like in the image above.
Can you help me please 😞 ? Merci d'avance !
Hey @s44
sorry for the delay.
Here are some screenshots and a detailed explanation how to get dynamic columns from SharePoint values:
1. My trigger is "Manually trigger a flow", from your response I understand, that your flow needs to run, whenever there is a change within the SharePoint List, so you should use something like "when an item is created or modified".
2. "Initialize variable" action -> Create a String variable, we need later:
3. "Get items" from SharePoint -> We get all the items from the SharePoint list:
4. "Select" -> From the array we receive from "Get items", we just need the distinct values from our column (in my case its a single line of text column "Title", choose your column within the "map" section). This will return an array with just the values from "Title".
5. "Parse JSON" and "Union()"expression -> I combined this step, of course you could also first use the "Union()" expression within a "Compose" action, but it's more like an unnessecary step. Union will combine two arrays and will return only distinct values. We union the array we created within "Select" with itself:
Share
Report
All responses (
Answers (
union(body('Select'),body('Select'))To get the schema, you can use the "Generate from sample". To get this, consider to run the "union()" expression within a "compose" action before, get the output of the compose action and paste it in the "Generate from sample" section.
6. "Apply to each" - "Append to string variable" -> To create columns within excel, you would need it in a specific format, so you can append every "title" from the array into the string variable we created in the beginning. The "Parse JSON" will give you the option to select the values from the "dynamic value" section. The "apply to each" will be created automatically, when you select "title" from the "dynamic values":
7. "Compose" - length(<array>) -> We also need to define the table range, to do this, you can use the "length()" expression to get the number of items within the distinct array:
8. "Compose" - if() -> Unfortunately there is no such function "char()" as in Power Apps, that enabled us to transform ASCII code into ASCII characters, to we cannot calculate the letter and need to use combined "if()" expressions. You can either use a "Compose" action or a variable to save this letter in:
if(
equals(outputs('Compose'),1), 'A',
if(
equals(outputs('Compose'),2), 'B',
if(
equals(outputs('Compose'),3), 'C', 'D'
)
)
)Now we do have all components we need to create the table within Excel:
9. "Create table" in Excel -> Select your location and the file, where you want to add this table. Your "table range" is starting at A1 and is ending at column <Compose 2 value> and your columns names is your string variable:
Also the range is first just the headers, so "A1:B1" (as an example). Afterwards you would need to use the Excel action "add a row into a table" to add all of your SharePoint items into the excel file.
10. "Add a row into a table" -> Last but not least, you need to add content into your Excel file. Select the same file, you created the table in. For the table name, you need to use a custom value, like the dynamic value from "Create table" or just the same name (because the table doesn't exist yet, so you cannot select it). As "Row" you need a valid JSON input like:
I just used your examples from above, so "Beurrerie 2" and "Beurrerie 4" are the column names we dynamically created and "Test" is the value. You can also create this JSON dynamically, similar to the actions we already used above. This will give you an Excel file like:
Source SharePoint List:
Note: it's pretty difficult to create an Excel file using Power Automate. If you would need a new one every time you should consider to create an empty Excel template and save it somewhere in SharePoint/OneDrive and just create a copy of this file whenever you need it. Also your flow maybe run into problems, if the table range is already taken, therefore it's probably better to create a new document, because you cannot delete an existing table from Power Automate (maybe using Office scripts, but I did not tested this).
Does this help you? Otherwise please give me some more information.
Best regards
Marvin
If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.
Blog: Cloudkumpel
No I have to use Power Automate to do what I want to do. Because all the values that are in my "Sous-fFilière" field on my SharePoint list must become columns on the Excel file. And I need to have an Excel file each time an item is modified or created in the Sharepoint list. And then anyway my client wants me to do a Power Automate flow ^^" @Anonymous
First, thank you for your response. 🙂 I would like to do everything dynamically.
Could you show me examples with screenshots of the blocks on Power Automate? I don't quite understand how to do it... @MarvinBangert
Why would you use a flow for this? You can get the Sharepoint data into an excel (get Data) and after that use an excel feature called pivot table. That way you are able to click refresh in excel and have it all there.
Hey @s44
are the column names always the same within the excel table? To create the table, you can create the excel action "Create table", if the columns are always the same, you can just enter them within the "Columns names" section. If they should be more dynamically (like only the values that are within the SharePoint column "Sous-Filière"), you would need more actions (like dynamically getting the values from SharePoint list, making them distinct, adding it to a string, so that you can use them while creating the tables). It's easier if you have a pre-defined table, otherwise making the table range dynamic is also a bit challenging but possible (using IFs and length of the distinct array).
The table range notation should be just the header, so you can just "add a row into a table".
When you have created your table, you need to add the rows, so you should consider looping through every niveau level and adding the values from SharePoint into this.
Please let me know if you need more assistance, then I will need to build this in some more detail and maybe need some more information about where you are struggling.
Does this help you? Otherwise please give me some more information.
Best regards
Marvin
If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.
Blog: Cloudkumpel
#1
Michael E. Gernaey
497
Super User 2025 Season 2
#2
David_MA
436
Super User 2025 Season 2
#3
Riyaz_riz11
244
Super User 2025 Season 2
