Custom Columns with conditional statement for quote and product offering type

[Anonymous]

This is my first post, thank you all sharing your knowledge and support! I have been struggling with these two columns in power query and need your help

I have simplified and replicated my data into a simple table as follows:

Quote_Nr	Revenue Amount	Product	Type of Quote offering	Type of Product Offering
1001	500000	Automation Solution	Stand alone	Standard
1002	200000	Propulsion	Package	Standard
1002	0	Vessel App	Package	Give away
1003	400000	Machinery	Stand alone	Standard
1004	500000	Automation Solution	Stand alone	Standard
1005	200	Vessel App	Stand alone	Standard
1006	500000	Automation Solution	Package	Standard
1006	200000	Propulsion	Package	Standard
1006	1	Vessel App	Package	Give away
1007	200	Vessel App	Stand alone	Standard
1008	500000	Automation Solution	Package	Standard
1008	400000	Machinery	Package	Standard
1008	200000	Propulsion	Package	Standard
1008	1	Vessel App	Package	Give away
1009	500000	Automation Solution	Package	Standard
1009	0	Vessel App	Package	Give away

I am missing the last two columns on the right, "Type_of_quote_offering" and "Type_of_product_offering".

As you can see, the "Type of quote offering" column depends on the number of products offered on each quote ID "Quote_nr", would it be possible from someone here to help me to build a custom column with the necessary code/formula on the query editor to distinguish whether if it is a "package" quote or a "Stand alone" quote offer.

On the other hand "type of product offering" is related to the amount on each row, It is considered a "Give away" any product with cero or 1 dollar as an inputted amount on the system. Same case, would it be possible to distinguish between "Standard" and "Give away" with an if-else clause in the query editor to create this column? and how?

I really appreciate your support on this one!

David

Maybe some of the top master can spot this messege :)‌ like  @Jimmy801  @edhans @Anonymous  @Fowmy @ImkeF 

[Anonymous · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUTI1AAEgw7G0JD83sSQzP08hOD+nFMRQitUBqzMCShvB1AUU5ReU5hSjSYNkwlKLi1NzFBwLCmAyxkBRE5hG38TkjMy81KJKmKwJkdabQqzHaoMZkWaY4fcCSNoQqwXmeCy3INJyC7zBYIHfaRY4nWZJpPWWWOInFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quote_Nr = _t, #"Revenue Amount" = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Revenue Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Quote_Nr"}, {{"Type Of Quote Offering", each if Table.RowCount(_)> 1 then "Package" else "Stand Alone", Int64.Type}, {"all", each _}}),
    #"Expanded all1" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Revenue Amount", "Product"}, {"Revenue Amount", "Product"}),
    #"Added Custom" = Table.AddColumn(#"Expanded all1", "Type of Product Offering", each if [Revenue Amount]<2 then "Give Away" else "Standard"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Quote_Nr", "Revenue Amount", "Product", "Type Of Quote Offering", "Type of Product Offering"})
in
    #"Reordered Columns"

[Anonymous]

I implemented it in the real data set and worked great! thank you @Anonymous