Log In
Register
Home
June 17, 2020
Offline
Hi!
I have SQL query which produces PIVOT on dates.
SQL columns(variables) names are dynamic (['+ CONVERT(VARCHAR(7),DATEADD(month,-36,GETDATE()), 120)+']).
It produces 18 months of sales data labeled as below:
[2021-09], ...,[2023-02].
2023-04 it will be [2021-10], ...,[2023-03].
After refreshing data via Power Query Excel formula
=((SUMA.JEŻELI(SPRZEDAŻ_HD_PST[@[2021-09]:[2023-02]],">0")+(1E-64))/(LICZ.JEŻELI(SPRZEDAŻ_HD_PST[@[2021-09]:[2023-02]],">0")+(1E-64)))
fails due to changed column names.
Any hints how to overcome it?
My ideas are:
1. Write down SQL query, which produces desired average (sum.if [2021-09]:[2023-02]],">0" / count/if [2021-09]:[2023-02]],">0") - (1E-64) part is there just to avoid dividing by zero and generating error this way - where columns(variables) names are dynamic.
2. Add a record with constant columns names eg.: M1, ..., M18 and use above Excel formula. In this case data downloaded via Power Query should be written down starting in A2 cell, not in A1 cell. Do not not how to do it.
Kind regards,
Luke
October 5, 2010
Offline
Hi Lukasz,
So you are writing SQL to query a database where the column names are changing?
How does that work? How are column created/deleted in the database continuously? It's a bad idea having column names that keep changing. Your best solution is to not do this making any queries to the db much easier to write.
Regards
Phil
1 Guest(s)
