Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Dynamic columns in table or matrix visualization

I need to ask you for help, with a requirement that may look easy, but it has me spinning.

I have a report that they give me of accounting, which shows the correlation of the last months processed for companies, for each branch and with its code.

The detail is that I was asked to load this "summary" as it is in a powerbi report, plus the total per column, of those with values. The detail of all this is that the columns of months are changing their name, according to the month in which it is processed:

In the example of the photo, it is processed from Oct 22 backwards (it is not in order), then, for the next process of the following month, these columns change name and only keeping as constant name the columns "Dif, Average, Dif Average, Withheld, Preinvoiced, Manual and Contract"... Then, for the next period the columns could be renamed to "Jan 23, Dec 22, Nov 22.... etc"

Then... How could I get the names of the columns and be able to create a visualization of tables, which loads me the same information, but dynamically absorbing the names of the columns and showing them in the visualization, showing the total at the end, for the fields that are numeric.

I hope you understand what I need and first, thanks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi vfh_2023,

    Did you mean to dynamic rename these date fields based on current system date? If that is the case, you can try to enter to query editor to add rename column step based on M query functions.

    Before

    After

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column2", Date.ToText(Date.From(DateTime.LocalNow()),"MMM-yy")}, {"Column3", Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),1),"MMM-yy")}, {"Column4", Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),2),"MMM-yy")}})

    Regards,

    Xiaoxin Sheng

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      Thank you very much for this information, but my problem does not come from wanting to change the name of the column dynamically, but when adding the columns in a visualization, since when changing the name every month, this will give me an error at the time that the name of that column is another.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi vfh_2023, 

        >>but when adding the columns in a visualization, since when changing the name every month, this will give me an error at the time that the name of that column is another.

        I think this is an expected issue if the source table field names will change dynamically.

        AFAIK, the query tables will cache the table structure(fields name, type) into the steps, and they did not synced fields names when the source fields name changes. 

        Regards,

        Xiaoxin Sheng