cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Custom Columns with conditional statement for quote and product offering type

 

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_NrRevenue AmountProductType of Quote offeringType of Product Offering
1001500000Automation SolutionStand aloneStandard
1002200000PropulsionPackageStandard
10020Vessel AppPackageGive away
1003400000MachineryStand aloneStandard
1004500000Automation SolutionStand aloneStandard
1005200Vessel AppStand aloneStandard
1006500000Automation SolutionPackageStandard
1006200000PropulsionPackageStandard
10061Vessel AppPackageGive away
1007200Vessel AppStand aloneStandard
1008500000Automation SolutionPackageStandard
1008400000MachineryPackageStandard
1008200000PropulsionPackageStandard
10081Vessel AppPackageGive away
1009500000Automation SolutionPackageStandard
10090Vessel AppPackageGive 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 @Rocco_sprmnt21  @Fowmy @ImkeF 

1 ACCEPTED SOLUTION

Accepted Solutions
Memorable Member
Memorable Member

 

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"

 

View solution in original post

2 REPLIES 2
Memorable Member
Memorable Member

 

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"

 

View solution in original post

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors