Column name changes everymonth or quarter

Question

hi Expert

i created a yearly reporting flow based on quarter but due to error in name the value is stuck in a flow. Is there any way to make the column name dynamic which changes according to the month.

Sample data.xlsx

danilang · Answer

Hi @pokhan27

Apply the final cross tab after your summarize tools instead of before.  In the summarize tools, Group By Region Name and Quarter.  You can also combine the two summarize tools into one like this

Do the cross tab after this

Dan

pokhan27 · Answer

I did do that in the transpose way.

just sharing you a sample flow. I am having issue in while summing.

sample flow.yxmd

danilang · Answer

Hi @pokhan27

Here's a sample that demonstrates two patterns that you can use.

1. If your input has varying numbers of value columns, use a Transpose/Crosstab pattern

The config of the transpose looks like this

As long as you have the Dynamic or Unknown Columns checked any new columns will be automatically included in the transposed columns.  In the CrossTab tool, group by the same columns you used as keys in the Transpose tool, use Name as the Column Headers and Value as the Column Values using Sum as the Method

Your output will look like this

2. If your input has a specific column that can change names over time use a dynamic rename pattern

Use a Field Info tool, a Record ID and Formula tool to the build a list of the existing column names and a list of new names, renaming the target one to [_SummaryColumn_].   Use a Dynamic rename in "Take Field Names from Right Input Rows" mode to rename the target.  Apply the Summary to the [_SummaryColumn_] and use a second Dynamic Rename to restore the original field names

Dan

VaryingColumnNames.yxmd