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,
I have the following source table that I import into Power BI:
I then have some Power Query (M) code that takes that source data (pivots it etc..) and then groups that table by Region and then subtracts Costs from Sales (Sales - Costs) creating a new column:
#"Filtered RowsSSGP" = Table.SelectRows(#"Pivoted Column", each ([Grouping_Level] = "Costs" or [Grouping_Level] = "Sales")),
#"Grouped RowsSSGP" = Table.Group(#"Filtered RowsSSGP", {"Region"}, {{"All", each _, type table}}),
#"Added CustomSSGP" = Table.AddColumn(#"Grouped RowsSSGP", "DEValue", each [All]{[Grouping_Level="Sales"]}[DEValue]-[All]{[Grouping_Level="Costs"]}[DEValue]),
#"Added Custom1SSGP" = Table.AddColumn(#"Added CustomSSGP", "DETarget", each [All]{[Grouping_Level="Sales"]}[DETarget]-[All]{[Grouping_Level="Costs"]}[DETarget]),
#"Added Custom2SSGP" = Table.AddColumn(#"Added Custom1SSGP", "MTDValue", each [All]{[Grouping_Level="Sales"]}[MTDValue]-[All]{[Grouping_Level="Costs"]}[MTDValue]),
#"Added Custom3SSGP" = Table.AddColumn(#"Added Custom2SSGP", "MTDTarget", each [All]{[Grouping_Level="Sales"]}[MTDTarget]-[All]{[Grouping_Level="Costs"]}[MTDTarget]),
#"Added Custom4SSGP" = Table.AddColumn(#"Added Custom3SSGP", "Grouping_Level", each "SSGP"),
#"Removed ColumnsSSGP" = Table.RemoveColumns(#"Added Custom4SSGP",{"All"}),
#"Appended QuerySSGP" = Table.Combine({#"Removed ColumnsSSGP", #"Pivoted Column"}),
However, not all Regions have a Sales or Costs entry (an actual entry and not just value) and therefore an error shows for the Regions where the calculation cannot be completed, as the Region doesn't have a Sales and Costs field (as indicated by the Grouping_Level column in the source data):
How do I place a check in Power Query so to do the following:
1) If the Sales and Costs values are present for the Region and Grouping_Level then calculate Sales - Costs (well the PQ code does this already - so this one is done).
2) If the Sales value (but not the Costs) is present then return the Sales value alone, or if the Costs value (but not the Sales) is present then return the Costs value alone.
3) Is neither the Sales and Costs values are present then return a 0 as the overall calculation for the created SSGP column.
If someone can help me amend my code to reflect the above requirements I would be very grateful.
Thanks in advance.
Just to help show what I'm trying to achieve a little more, take as look at the below example:
#"Filtered RowsSSGP" = Table.SelectRows(#"Pivoted Column", each ([Grouping_Level] = "Costs" or [Grouping_Level] = "Sales")),
#"Grouped RowsSSGP" = Table.Group(#"Filtered RowsSSGP", {"Region"}, {{"All", each _, type table}}),
#"Added CustomSSGP" = Table.AddColumn(#"Grouped RowsSSGP", "DEValue",
each if [Grouping_Level] = "Sales" then
each if [Grouping_Level] = "Costs" then
each [All]{[Grouping_Level="Sales"]}[DEValue]-[All]{[Grouping_Level="Costs"]}[DEValue]
else
each [All]{[Grouping_Level="Sales"]}[DEValue]
else
each if [Grouping_Level] = "Costs" then
each [All]{[Grouping_Level="Costs"]}[DEValue]
else
0
),
The above doesn't work and neither do I expect it to, 'each if [Grouping_Level] = "Sales' I expect isn't the right code and I expect Table.Contans or similar is needed, but it should show you what I require a little more clearer.
Please help?
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |