Help Please on Prepaid expenses

[JamesFR06]

Hello community,

I need help on Query. I want to make a BI to manage Prepaid expenses.

I have 2 tables.

1) CCA

2) a date table linked by the field Date to CCA on Date field.

My wish is to create in Power Query a new table that summarize for each CCA form Date Début to Date Fin a record for each date with Cout_journalier for value.

Final result to be clear would be something like this :

IDCCA	DATE	Cout_journalier
1	05/01/2024	33,33
1	06/01/2024	33,33
1	07/01/2024	33,33

Here is my sample Data :

IDCCA DATE Journal CP Frs Fournisseur Libellé Analytrique Date Début Date Fin Montant NbJours Cout_journalier
1 01/01/2024 HA 401DEDE DEDE Mais qui c'est dédé ADM 05/01/2024 04/04/2024 3000 90 3,33333E+16
2 02/01/2024 HA 401DUDU DUDU Mais qui c'est dudu ADM 09/01/2024 08/04/2024 6000 90 6,66667E+16
3 02/01/2024 HA 401EDF EDF EDF ADM 09/01/2024 08/04/2024 6000 90 6,66667E+16

THanks in advance.

James

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFySSxJVXA5vDKptATGc8vMAzJ98/NKEvNKlGJ1opUMgXwDU30DQ30jAyMTEMdEH4igHGMDAwOwMiOQjCWyMgskZWYwZcZ4lVmClcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Date Début", type date}, {"Date Fin", type date}, {"Montant", Int64.Type}},"fr"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Date Début],1+Int16.From([Date Fin]-[Date Début]),#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[JamesFR06]

@lbendlin ,

Thank you very much for your help. It works perfectly!

Have a nice weekend.

James