Python in Power Query VS Code

[WilliamHill99]

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

[v-sgandrathi]

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!

[v-sgandrathi]

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.

[WilliamHill99]

@lbendlin ,

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

DATE_ID	Town-Citycode	SALECOUNT
1/25/2026	TownAAAAA-1111	400
1/25/2026	TownBBBBB-2222	500
1/25/2026	TownCCCCC-3333	200
1/26/2026	TownAAAAA-1111	300
1/26/2026	TownBBBBB-2222	500
1/26/2026	TownCCCCC-3333	199
1/27/2026	TownAAAAA-1111	122
1/27/2026	TownBBBBB-2222	500
1/27/2026	TownCCCCC-3333	333

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

[ThxAlot · Accepted solution]

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,

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...