@MichaelBailey1 This is how I would build my flow. We can add as many Date columns as we want, and the flow will still produce the proper output.
I have an Excel Table with the following data.
Below is the full flow. I'll go into each of the actions.
List rows present in a table returns all the Excel Table data.
Before we move onto the next step, I just wanted to show you what output we get from this, especially the auto-generated columns. Below is the data for the first object.
{
"@odata.etag": "",
"ItemInternalId": "0463dc90-b6b5-405b-be61-b07260a8afd0",
"Category": "Cat 1",
"Oct-22": "15",
"Nov-222": "39",
"Dec-22": "41"
}
Compose extracts out the data from the first object excluding the first three properties (the first two properties will be the auto-generated ones that Excel produces, and the third will be the Category - as shown in the object above). Effectively we want to get a list of all the Date Column Headers. The expression I use here is:
skip(split(replace(string(first(outputs('List_rows_present_in_a_table')?['body/value'])), '"', ''), ','), 3)
If we work from inside out, we can see that we get the first object, then convert it to a string, then replace the " with an empty string (effective removing the "), split by comma, and skip the first three properties.
The result of this would be something like that below. You'll note that we still need to clean it up a bit to get just the dates.
[
"Oct-22:15",
"Nov-222:39",
"Dec-22:41}"
]
Next, we use a Select using substring to get the Dates only. Each row will get all characters up to the colon :
substring(item(), 0, indexOf(item(), ':'))
The output would be:
[
"Oct-22",
"Nov-222",
"Dec-22"
]
We then initialize an array variable called data that will eventually contain all the objects.
Our Apply to each takes in the output from our Select so that we iterate over each of the Months.
Our Select Month Data uses the value from List rows present in a table and builds up an object with the properties Category, Month, and Value.
Category uses the Category field from List rows present in a table.
Month uses the Current Item which is the expression below. Effectively, it gets the current Month we are looping over.
items('Apply_to_each')
Value comes from List rows present in a table but passes in the Month to dynamically get the appropriate property value. It uses the following expression:
item()?[items('Apply_to_each')]
This will give us an array of objects for the current Month we are iterating over.
Next, we use another Compose to join this array with the array variable, so we end up with an array of objects from all months. The expression used here is:
union(body('Select_Month_Data'), variables('data'))
We then set the data array variable to the output of the union using Set variable data.
We now have all the objects stored in the data array. The Compose after the Apply to each is just there to show the data in the array.
The final output for the three Months is below:
[
{
"Category": "Cat 1",
"Month": "Dec-22",
"Value": "41"
},
{
"Category": "Cat 2",
"Month": "Dec-22",
"Value": "1"
},
{
"Category": "Cat 3",
"Month": "Dec-22",
"Value": "1"
},
{
"Category": "Cat 4",
"Month": "Dec-22",
"Value": "0"
},
{
"Category": "Cat 5",
"Month": "Dec-22",
"Value": "25"
},
{
"Category": "Cat 6",
"Month": "Dec-22",
"Value": "19"
},
{
"Category": "Cat 7",
"Month": "Dec-22",
"Value": "18"
},
{
"Category": "Cat 8",
"Month": "Dec-22",
"Value": "13"
},
{
"Category": "Cat 9",
"Month": "Dec-22",
"Value": "33"
},
{
"Category": "Cat 10",
"Month": "Dec-22",
"Value": "3"
},
{
"Category": "Cat 11",
"Month": "Dec-22",
"Value": "39"
},
{
"Category": "Cat 12",
"Month": "Dec-22",
"Value": "25"
},
{
"Category": "Cat 1",
"Month": "Nov-222",
"Value": "39"
},
{
"Category": "Cat 2",
"Month": "Nov-222",
"Value": "48"
},
{
"Category": "Cat 3",
"Month": "Nov-222",
"Value": "5"
},
{
"Category": "Cat 4",
"Month": "Nov-222",
"Value": "32"
},
{
"Category": "Cat 5",
"Month": "Nov-222",
"Value": "42"
},
{
"Category": "Cat 6",
"Month": "Nov-222",
"Value": "3"
},
{
"Category": "Cat 7",
"Month": "Nov-222",
"Value": "20"
},
{
"Category": "Cat 8",
"Month": "Nov-222",
"Value": "14"
},
{
"Category": "Cat 9",
"Month": "Nov-222",
"Value": "14"
},
{
"Category": "Cat 10",
"Month": "Nov-222",
"Value": "4"
},
{
"Category": "Cat 11",
"Month": "Nov-222",
"Value": "18"
},
{
"Category": "Cat 12",
"Month": "Nov-222",
"Value": "12"
},
{
"Category": "Cat 1",
"Month": "Oct-22",
"Value": "15"
},
{
"Category": "Cat 2",
"Month": "Oct-22",
"Value": "7"
},
{
"Category": "Cat 3",
"Month": "Oct-22",
"Value": "59"
},
{
"Category": "Cat 4",
"Month": "Oct-22",
"Value": "58"
},
{
"Category": "Cat 5",
"Month": "Oct-22",
"Value": "15"
},
{
"Category": "Cat 6",
"Month": "Oct-22",
"Value": "49"
},
{
"Category": "Cat 7",
"Month": "Oct-22",
"Value": "48"
},
{
"Category": "Cat 8",
"Month": "Oct-22",
"Value": "20"
},
{
"Category": "Cat 9",
"Month": "Oct-22",
"Value": "6"
},
{
"Category": "Cat 10",
"Month": "Oct-22",
"Value": "39"
},
{
"Category": "Cat 11",
"Month": "Oct-22",
"Value": "20"
},
{
"Category": "Cat 12",
"Month": "Oct-22",
"Value": "34"
}
]
