Forum Discussion
Using If and then finding multiplication of two different rows given the same ID number.
Hi,
I am trying to find the M2 given a particular varible. Currently I have split up the Values of Width and Height. However, it has applied the Job Id multiple times. So 1 Column has a Job ID twice. Width and Height sit on different rows.
I have another DB / Table that holds the Job ID. So I attempted to do a lookupvalue taking the Job Id with the Width and applying the Width into that table. However, for one reason or another this came back with a spelling issue..... Tables are JobDetails2019 & Jobs2019.
What I need to achieve is If col 4 = "Spray Paint" take (Width * Height Divided 10000) * Col 5. If false take Width * Height divided by 10000. In a different column
Col 5 is incidents.
Any help on this would be phenominal.
Cheers,
Rob
Hi Anonymous ,
Please check the following steps as below.
1. Group the table by ID column in power query as below.
2. Expand the column and add a custom column as below.
=if [new.c4] = "Spray Paint" then ([width]*[height] /10000)*[column 5] else [width]*[height] /10000Also M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY1MjAwVdJRQkGxOthkgguKEisVAhIz80pwK8IrY4wqZWhggFefCbI8GdYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, c1 = _t, c2 = _t, c3 = _t, c4 = _t, c5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"c1", Int64.Type}, {"c2", Int64.Type}, {"c3", type text}, {"c4", type text}, {"c5", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"width", each List.Max([c1]), type number}, {"height", each List.Max([c2]), type number}, {"new", each _, type table [ID=number, c1=number, c2=number, c3=text, c4=text, c5=number]}, {"column 5", each List.Max([c5]), type number}}), #"Expanded new" = Table.ExpandTableColumn(#"Grouped Rows", "new", {"c4"}, {"new.c4"}), #"Added Custom" = Table.AddColumn(#"Expanded new", "Custom", each if [new.c4] = "Spray Paint" then ([width]*[height] /10000)*[column 5] else [width]*[height] /10000) in #"Added Custom"
2 Replies
See below. Everything in brackets is a column name and I had to guess since your image has no column names. Everything is case sensitive. If is not the same as if for example.
if [Column4] = "Spray Paint" then ([Width] * [Height] / 10000) * [Column5] else [Width] * [Height] / 10000Hi Anonymous ,
Please check the following steps as below.
1. Group the table by ID column in power query as below.
2. Expand the column and add a custom column as below.
=if [new.c4] = "Spray Paint" then ([width]*[height] /10000)*[column 5] else [width]*[height] /10000Also M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY1MjAwVdJRQkGxOthkgguKEisVAhIz80pwK8IrY4wqZWhggFefCbI8GdYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, c1 = _t, c2 = _t, c3 = _t, c4 = _t, c5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"c1", Int64.Type}, {"c2", Int64.Type}, {"c3", type text}, {"c4", type text}, {"c5", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"width", each List.Max([c1]), type number}, {"height", each List.Max([c2]), type number}, {"new", each _, type table [ID=number, c1=number, c2=number, c3=text, c4=text, c5=number]}, {"column 5", each List.Max([c5]), type number}}), #"Expanded new" = Table.ExpandTableColumn(#"Grouped Rows", "new", {"c4"}, {"new.c4"}), #"Added Custom" = Table.AddColumn(#"Expanded new", "Custom", each if [new.c4] = "Spray Paint" then ([width]*[height] /10000)*[column 5] else [width]*[height] /10000) in #"Added Custom"
