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.
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
Solved! Go to 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"
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"
I implemented it in the real data set and worked great! thank you @Anonymous
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 |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |