Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Missing groupby columns while using pandas dataframe

Hi Experts,   I am trying the PoC of a project  via loading data from excel file(just taking excel as an example.) and transform in python dataframe, then load to Power bi. . It runs well in PyDev....
General Comment
Need Help
Show and Tell
Tutorial Requests
lbendlin's avatar
lbendlin
Super User
3 years ago

Look at your Python output.  It is pivoted which results in the column names for Date and Sales moving down a row.  When Power Query then tries to read the data it cannot find the column names.

 

Feed the raw data to Power Query, not the processed data.

 

By the way, none of this requires Python. It will be much faster with native functions.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks lbendlin. Yes I noticed it. I think it is due to how Power BI interpretes the dataframe which is pivtol. I did export the groupbyed dataframe to csv. the format is correct.  For the above scenario, your are right, no need of python.  but the project might have more complex logics, for example , the order of different queries, and the results criterias matching.  I had a post before, but didn't seem to be possible just in PQ or PBI domians.

    https://community.powerbi.com/t5/Power-Query/How-to-get-the-refresh-status-of-each-power-query-of-a-excel/m-p/2602096#M79383

    The current approach is to use Python to proscess the data and load to PBI for visualization. 

     

    Any other better solution?

     

    Thanks!

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      Power Query and VBA do not mix well.  They have very different philosophies.  I assume you have seen this advice

       

      the BackgroundQuery=True must be set. otherwise, if it set to False the afterfresh event will not be triggered