Forum Discussion
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 ...
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 ,
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
Super User
Super Userpy 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,