Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, this is my existing table, which aggregate excel files for each months. Excel files are cumulative data so I would like to have diffrence between two periods (last period).
I think it should be possible in langage m, I should order my table on Account and date but how can add a column which will do this:
if account row before is same value as actual row then calculalte row[Amount] - row before[Amount]
thanks
Solved! Go to Solution.
I find a solution with creating an index column and merge with my table with herself.
I find this to help me
My code:
#"Lignes filtrées" = Table.SelectRows(#"Lignes triées", each ([Account] <> null)), #"Index ajouté" = Table.AddIndexColumn(#"Lignes filtrées", "Index", 1, 1, Int64.Type), #"Addition insérée" = Table.AddColumn(#"Index ajouté", "Addition", each [Index] + 1, type number), #"Requêtes fusionnées1" = Table.NestedJoin(#"Addition insérée", {"Index"}, #"Addition insérée", {"Addition"}, "Addition insérée", JoinKind.LeftOuter), #"Addition insérée développé" = Table.ExpandTableColumn(#"Requêtes fusionnées1", "Addition insérée", {"Account", "Amount"}, {"Addition insérée.Account", "Addition insérée.Amount"}), #"Colonnes supprimées" = Table.RemoveColumns(#"Addition insérée développé",{"Index", "Addition"}), #"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes supprimées", "Variation", each if [Addition insérée.Account] <> null
I find a solution with creating an index column and merge with my table with herself.
I find this to help me
My code:
#"Lignes filtrées" = Table.SelectRows(#"Lignes triées", each ([Account] <> null)), #"Index ajouté" = Table.AddIndexColumn(#"Lignes filtrées", "Index", 1, 1, Int64.Type), #"Addition insérée" = Table.AddColumn(#"Index ajouté", "Addition", each [Index] + 1, type number), #"Requêtes fusionnées1" = Table.NestedJoin(#"Addition insérée", {"Index"}, #"Addition insérée", {"Addition"}, "Addition insérée", JoinKind.LeftOuter), #"Addition insérée développé" = Table.ExpandTableColumn(#"Requêtes fusionnées1", "Addition insérée", {"Account", "Amount"}, {"Addition insérée.Account", "Addition insérée.Amount"}), #"Colonnes supprimées" = Table.RemoveColumns(#"Addition insérée développé",{"Index", "Addition"}), #"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes supprimées", "Variation", each if [Addition insérée.Account] <> null
@phaum1967 , add index column in power query
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
And this formula in dax
new column = [Amount] -sumx(filter(Table , [index] = earlier([index]) -1),[Amount] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |