Refresh (new column) Excel database - possible to keep unpivoted columns (and add the new ones)?

[BMaurus]

Hi Power BI - Pros,

I get monthly data and add it (new column) to the table below. I built a comprehensive report in Power BI with this database and unpivoted the columns BD to BQ in order to have one "month"-field and one "value"-field.
How can I now automatically refresh this database in Power Bi (when I added a new column to the Excel sheet), so that the new column is added to the unpivoted "month" and "value" and I don't have to pivot/unpivot everything again?

Highly appreciate any tips and tricks :).

Cheers,

Benedikt

[ibarrau]

Hi. Can you show us your transformation? because I think it should refresh automatically even if you add a new month. I have a dataset similar to this one and the following transformation

= Table.UnpivotOtherColumns(Origen, {"Column1", "Column2"}, "Atribute", "Value")

All columns in the list will mantain and all the others (in this case months) will unpivot.

Regards,

[BMaurus]

Hi,

thanks for your quick reply.

Under "Applied steps" - "Unpivoted Columns" I got:

= Table.UnpivotOtherColumns(#"Changed Type", {"Zeile", "Überbezeichnung", "Bezeichnung", "Konto", "Spalte1", "Zeile2", "Konten-/#(lf)Zeilenbeschriftung"}, "Attribute", "Value")

If I refresh my data, I get the following error message in my graphs/tables:

If I look at my Visualization and Fields space, I can only select "Wert" as Sum, First, Last, Count of "Wert) which obviously doesn't make any sense...

[dax · Accepted solution]

Hi @BMaurus , 

Did you mean that after you apply "unpivot columns", the visual prompt error, right? I think this should be caused by column missing. When you choose columns to "unpivot columns", the chosen columns will be transformed to "Attribute" and "Value" column, so you might will get error in visual, please check this in visual and try to change fields or re-write the measure in visual. By the way, you also could check the detailed error information in visual and inform me in details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Ok. Can you show the "See details" message of the error? 

I will also ask you for the steps in power query (edit queries) after the unpivot. I doesn't look like the problem is the unpivot.

Try changing the "Value" type to number after the unpivot. The engine might be missing this assumption and that may be why you can't sum it after the refresh

Regards,