Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to create a new column in Power BI Desktop which replicates a basic Excel formula which seems simple enough.
The Excel formula which I am trying to replicate is in column 😧
=IF(B2=B1, 0, C2)
So that the returned column should look like column D.
I have the same data structure in Power BI and would like to create a new column to replicate column D in Excel, but I can't find a way to replicate the formula where it calls for :
B2=B1
I would be grateful for anyone's help.
Many thanks in advance!
Solved! Go to Solution.
You could shift one column using:
Shifted column = LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1)
or you could get the result directly by using:
Reported size = IF(Table1[Cage No]=LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1);0;Table1[Size])
Hi,
This can also be solved with the following M code
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index Order", Int64.Type}, {"Cage No", type text}, {"Size", Int64.Type}}),
Partition = Table.Group(#"Changed Type", {"Cage No"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index Order", "Size", "Index"}, {"Index Order", "Size", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition",{{"Index", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Index]=1 then [Size] else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns"
You are welcome. If my reply helps, please mark it as Answer.
You could shift one column using:
Shifted column = LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1)
or you could get the result directly by using:
Reported size = IF(Table1[Cage No]=LOOKUPVALUE(Table1[Cage No];Table1[Index Order];Table1[Index Order]-1);0;Table1[Size])
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |