Forum Discussion
Pandas (Python) in powerBI LOOP
Good evening please i need your help I wrote this code to calculate the final stock; the problem is that I want to apply a loop on 'code article' and 'division'. each article in one of its two divisions has its own stock
the stock of the article in the 2 divisions is independent
'dataset' holds the input data for this script
import pandas as pd
DX= pd.DataFrame(dataset.loc[:,])
AAA = []
for i in range(len(DX)):
if(i==0):
var = DX['Stock 10 fixe'][0] + DX['Livraison'][i]-DX['consomation'][i]
AAA.append(var)
else:
var = var + DX['Livraison'][i]-DX['consomation'][i]
AAA.append(var)DX['stock Final']=AAA
the result of my code is false (only good for M100 and DPC1 i want that ( every 'code article' in every 'Division' has his own stack of the last 4 months
Hi Anonymous ,
I'm not sure if it's possible to achieve the result you want with phython, but it can be achieved with DAX... I create a sample pbix file(see attachment), please check whether it can work as a workaround.
1. Add Index column group by Code Aritcle and Division according to month (as shown below) in Power Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZA9D4MgEIb/C7O5wHEKznUi6cduHKph6NCaGOPvL2iaGBAaWQ4uD8fD27bsKjhnBWseF+EKluvpPsxjbye3EwJIucpZV4Sw397Gxb77yfq7Gmq9zUjAjR1+sCSo1gd5CjbPz/LaHCoNvkvhYOO7UobKBLqMlc2BskQgryxDC3OgrDjo2isTJeCdstKAXkKU9Qbj/n/qT8x4JuYIzsUcwZmYcf+/yq1czHgm5gjOxRzBqZi7Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code Article" = _t, Division = _t, #"Stock 10 fixe" = _t, Month = _t, consomation = _t, Livraison = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code Article", type text}, {"Division", type text}, {"Stock 10 fixe", Int64.Type}, {"Month", type text}, {"consomation", type number}, {"Livraison", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Code Article", "Division"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table [Code Article=nullable text, Division=nullable text, Stock 10 fixe=nullable number, Month=nullable text, consomation=nullable number, Livraison=nullable number, Index=nullable number]}}), #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"}, {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"}) in #"Expanded Index"2. Create a measure or calculated column as below to get the final stock
Measure:
stock Final = VAR _selcodea = SELECTEDVALUE ( 'Table'[Code Article] ) VAR _seldiv = SELECTEDVALUE ( 'Table'[Division] ) RETURN CALCULATE ( SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] ) - SUM ( 'Table'[consomation] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Code Article] = _selcodea && 'Table'[Division] = _seldiv && 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] ) ) )Or Calculated column
Column_stockFinal = CALCULATE ( SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] ) - SUM ( 'Table'[consomation] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Code Article] = EARLIER('Table'[Code Article]) && 'Table'[Division] = EARLIER('Table'[Division]) && 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) )Best Regards
1 Reply
Hi Anonymous ,
I'm not sure if it's possible to achieve the result you want with phython, but it can be achieved with DAX... I create a sample pbix file(see attachment), please check whether it can work as a workaround.
1. Add Index column group by Code Aritcle and Division according to month (as shown below) in Power Query Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZA9D4MgEIb/C7O5wHEKznUi6cduHKph6NCaGOPvL2iaGBAaWQ4uD8fD27bsKjhnBWseF+EKluvpPsxjbye3EwJIucpZV4Sw397Gxb77yfq7Gmq9zUjAjR1+sCSo1gd5CjbPz/LaHCoNvkvhYOO7UobKBLqMlc2BskQgryxDC3OgrDjo2isTJeCdstKAXkKU9Qbj/n/qT8x4JuYIzsUcwZmYcf+/yq1czHgm5gjOxRzBqZi7Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code Article" = _t, Division = _t, #"Stock 10 fixe" = _t, Month = _t, consomation = _t, Livraison = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code Article", type text}, {"Division", type text}, {"Stock 10 fixe", Int64.Type}, {"Month", type text}, {"consomation", type number}, {"Livraison", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Code Article", "Division"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table [Code Article=nullable text, Division=nullable text, Stock 10 fixe=nullable number, Month=nullable text, consomation=nullable number, Livraison=nullable number, Index=nullable number]}}), #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"}, {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"}) in #"Expanded Index"2. Create a measure or calculated column as below to get the final stock
Measure:
stock Final = VAR _selcodea = SELECTEDVALUE ( 'Table'[Code Article] ) VAR _seldiv = SELECTEDVALUE ( 'Table'[Division] ) RETURN CALCULATE ( SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] ) - SUM ( 'Table'[consomation] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Code Article] = _selcodea && 'Table'[Division] = _seldiv && 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] ) ) )Or Calculated column
Column_stockFinal = CALCULATE ( SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] ) - SUM ( 'Table'[consomation] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Code Article] = EARLIER('Table'[Code Article]) && 'Table'[Division] = EARLIER('Table'[Division]) && 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) )Best Regards