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:
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
