Forum Discussion

WilliamHill99's avatar
WilliamHill99
Regular Visitor
4 months ago
Solved

Python in Power Query VS Code

Hi All,

 

I would like to fetch/ read csv file from local machine and performs pivot function in Python in PowerQuery. 

I tested my code in VScode and result is fine and expected as it is, but it does not happen with same code in Python Power Query .

 

code in VScode:

import pandas as pd
df = pd.read_csv('sample-data.csv')
df1 = pd.pivot_table (df, values='Total_Sale', index='Location',columns='DATE_ID', aggfunc='sum')
df1 =df1.reset_index()
df1["Town"]=df1["Location"].str[:10]
 
Code in Python Power query:

# 'dataset' holds the input data for this script
import pandas as pd

df1 = pd.pivot_table (dataset, index='Location',columns='DATE_ID',values='Total_Sale', aggfunc='sum',fill_value=0)
df1 =df1.reset_index()
df1["Town"]=df1["Location"].str[:10]

 

the output of Python Power Query is incomplete and somehow is not working as expected.

3 columns return as below.

Location | Microsoft.OleDb.Date | Town

Need Help
Tips and Tricks
  • ThxAlot's avatar
    ThxAlot
    4 months ago

    py script

    import pandas as pd
import numpy as np

df['DATE_ID'] = pd.to_datetime(df['DATE_ID'])
df['Town-Citycode'] = df['Town-Citycode'].str[:9]
df = df.sort_values(['Town-Citycode', 'DATE_ID'])

df['diff'] = np.where(df.groupby('Town-Citycode')['SALECOUNT'].diff()>0, 'Up', 'Down')
pvt = df.pivot_table(columns=['DATE_ID'], index=['Town-Citycode'], values=['SALECOUNT', 'diff'], aggfunc='sum')
pvt.reset_index(inplace=True)

     

    When it comes to data visualization, DAX is often a more elegant alternative,

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 months ago

    Why would you want to pivot the date column like that? That's antithetical to Power Query and Power BI. Your CSV file already seems to be in the best format.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • WilliamHill99's avatar
    WilliamHill99
    Regular Visitor
    4 months ago

    lbendlin ,

    I need be to in pivot and add columns as below ;  

     

    DATE_IDTown-CitycodeSALECOUNT
    1/25/2026TownAAAAA-1111400
    1/25/2026TownBBBBB-2222500
    1/25/2026TownCCCCC-3333200
    1/26/2026TownAAAAA-1111300
    1/26/2026TownBBBBB-2222500
    1/26/2026TownCCCCC-3333199
    1/27/2026TownAAAAA-1111122
    1/27/2026TownBBBBB-2222500
    1/27/2026TownCCCCC-3333333

    Sample input and Output as below ( D2-D1) is Day 2 minus Day 1
    sample input and output data

    • ThxAlot's avatar
      ThxAlot
      Super User
      4 months ago

      py script

      import pandas as pd
import numpy as np

df['DATE_ID'] = pd.to_datetime(df['DATE_ID'])
df['Town-Citycode'] = df['Town-Citycode'].str[:9]
df = df.sort_values(['Town-Citycode', 'DATE_ID'])

df['diff'] = np.where(df.groupby('Town-Citycode')['SALECOUNT'].diff()>0, 'Up', 'Down')
pvt = df.pivot_table(columns=['DATE_ID'], index=['Town-Citycode'], values=['SALECOUNT', 'diff'], aggfunc='sum')
pvt.reset_index(inplace=True)

       

      When it comes to data visualization, DAX is often a more elegant alternative,

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    4 months ago

    Hi WilliamHill99,

     

    Thank you ThxAlot  and lbendlin for your responses to the query.

    Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help. 

     

    Thank you.

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    4 months ago

    Hi WilliamHill99,


    we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

    Should you have any further questions, feel free to reach out.
    Thank you for being a part of the Microsoft Fabric Community Forum!