Future Monthly Salary Simulation with Power Query / Power BI

[@accfinmath3569]

@The Power User great work. I have learnt a lot about List.Generate by watching your videos. They are extremely helpful. Before watching your videos I did not know how to use List.Generate. After practicing the examples, I realised the concept behind it. I became brave and played around with it and modified the custom query that contains List.Generate function as following. I hope you don't mind me posting this here. = (x as number)=> let Max_Iteration = Table.RowCount(MonthlyIncrease), Test=List.Generate(()=> [Counter=0, Result= x, Value=0, Month=0], each [Counter]if current=C1{0} then state&{List.Last(state)+current} else state&{List.Last(state)*(1+current)}),2) in X), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom.1"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "Custom"), #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom",{"Salary"}), #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Name"}, {{"Ad", each _, type table [Name=text, Custom=number]}}), #"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Custom", each let Source = [Ad], Index = Table.AddIndexColumn(Source,"Ix",1,1) in Index), #"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Ad"}), #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns3", "Custom", {"Custom", "Ix"}, {"Custom.1", "Ix"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom1", {{"Ix", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom1", {{"Ix", type text}}, "en-US")[Ix]), "Ix", "Custom.1") in #"Pivoted Column" Increase (2) table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLQM1SK1YlWMgKxwSxjiKgpmGMCFzaFs8zgLHM4ywLOsgRrNwKzDQ3gwoaGCCbUrlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Value", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each "increase"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Category", "Month", "Value"}), #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Value"}), Value = #"Removed Other Columns"[Value] in Value

[@wynhopkins4023]

Excellent demo

[@ThePowerUser]

hey! thank you. Hope you enjoyed the video. :)

[@coollehavre]

Hi, great work and thanks, but how to get the percentage in the result of the function ? (to display this rate in the table)

[@ThePowerUser]

Hey! you'd need to modify the function or create a new custom function based on your specific requirements. It could output a record that contains multiple fields and then you'd expand such fields through the GUI

[@ExactProBi]

Thanks very good tip, can you please use zoom in effect to show small details when you work with various PQ windows?

[@ThePowerUser]

hey! thanks for watching the video. Could you give me an example of what scene should require the zoom in effect? Just don't wanna miss it for the next video

[@miltinhocamo3754]

Awesome Miguel... thanks a lot.

[@ThePowerUser]

hey! glad you liked it. What should I do next?

[@miltinhocamo3754]

When I share or move an Excel workbook file or a Power BI Desktop file which has something with power query I have to fix the path source in order to the power query run again. I would like to know how to built an M code to have a "Relative Path" or "Dinamic Path" so that to permit me move my files and don't generate errors in its power query connections. I tried following this suggestion: social.msdn.microsoft.com/Forums/sqlserver/en-US/399567de-b8e9-467a-a002-5a810ae21311/power-pivot-and-power-query-relative-path?forum=sqlkjpowerpivotforexcel but It doesn't work.... Is this possible?. Thanks in advance.

[@ThePowerUser]

I wouldn't recommend doing that if your end goal is to publish that solution to the Power BI service as the refresh will fail, but if you're simply trying to create a local desktop solution then this article will work for Excel workbooks: www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

[@miltinhocamo3754]

Ok... thank you Miguel.

[@pankaj007arbia]

Sir, Great work, if you kindly provide us the workbook too, it will be more useful to us.

[@ThePowerUser]

hey! Sadly I didn't save the workbook, but you can def try recreating the tables as they are quite small. Sorry!

[@coollehavre]

Hi, i would like to insert a condition in your function, but i don't know what's wrong: This is the code of the function : Could you help me ? (if i have the value "%" i want to do some calculation, otherwise, just add the amount) Thanks Source = Table.RowCount(Table.SelectRows(tAugmentation, each ([Category] = typeAugmentation))), Personnalisé1 = List.Generate ( ()=> [MoisEntree=0,Salaire=SalaireEntree,Taux=0,Categorie=typeAugmentation,Mois=Date.From(DateTime.LocalNow())], each [MoisEntree]= "%" then Salaire = [Salaire] * (1 + tAugmentation{[Index = [MoisEntree]+1]}[Augmentation]) else Salaire = [Salaire] + tAugmentation{[Index = [MoisEntree]+1]}[Augmentation]

[@ThePowerUser]

hey! The KZbin comments section is probably not the best for these type of questions. I'd recommend that you post your full scenario on official Power Query forum with sample data and expected output. Below is the link to the forum: community.powerbi.com/t5/Power-Query/bd-p/power-bi-services