March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Task: I need to assign a group to the main dataset depending on a table of conditions. If a condition is satified at the top, then it will not be allocated into a future group.
Main Dataset (Table Name: Source):
Fruit | Animal | Vehicle |
Apple | Lion | Car |
Apple | Mouse | Car |
Orange | Cat | Plane |
Pear | Cat | Train |
Condition Dataset (Table Name: Conditions):
Segment | Fruit | Animal | Vehicle |
1 | Cat | Plane | |
2 | Pear | ||
3 | Cat | Train | |
4 | Apple | ||
5 | Mouse | ||
6 | Orange | Cat | Plane |
Ideal Output:
Fruit | Animal | Vehicle | Segment Group |
Apple | Lion | Car | 4 |
Apple | Mouse | Car | 4 |
Orange | Cat | Plane | 6 |
Pear | Cat | Train | 2 |
I'm struggling to write this and I don't want to resort to hard-coding a long conditional entry with 35 groups that are likely to change / be adjusted.
Pseudo code is:
Table.AddColumn(Source, "Segment Group",
For each row where y = current row number
If Source[Fruit](y) = Conditions[Fruit](y) AND Source[Animal](y) = Conditions[Animal](y) AND Source[Vehicle](y) = Conditions[Vehicle](y) then Conditions[Segment](y)
Next y
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
CODE FOR SOURCE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lHyyczPA1LOiUVKsToIYd/80uJUJHH/osS8dIhACZAMyEnMSwVLBKQCVcCEQ4oSM/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Animal = _t, Vehicle = _t]),
ListValue = List.Buffer(Conditions[Value]),
ListSegment = List.Buffer(Conditions[Segment]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each try ListSegment{List.PositionOf(ListValue,[Fruit])} otherwise try ListSegment{List.PositionOf(ListValue,[Animal])} otherwise try ListSegment{List.PositionOf(ListValue,[Vehicle])} otherwise null)
in
#"Added Custom"
CODE FOR CONDITIONS
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIi58QSIBmQk5iXqhSrE61kBOKlJhZBZMFCxsgqQ4oSM/PAwiZAnmNBQU4qklJTCNs3v7Q4FSZmBmT4FyXmpaeiWxcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Fruit = _t, Animal = _t, Vehicle = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", Int64.Type}, {"Fruit", type text}, {"Animal", type text}, {"Vehicle", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Segment"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> null))
in
#"Filtered Rows"
Hi Vijay,
The List.Position seems to works on a column basis rather than row basis. Is that correct?
#"Added Custom" = Table.AddColumn(Source, "Custom", each try ListSegment{List.PositionOf(ListValue,[Fruit])} otherwise try ListSegment{List.PositionOf(ListValue,[Animal])} otherwise try ListSegment{List.PositionOf(ListValue,[Vehicle])} otherwise null)
It doesn't work how I'd need it to. Where in effect I have an AND statement between each column and an OR statement between each row.
With my code what output you get and what is the output needed, then accordingly I can suggest you the solution.
With my code what output you get and what is the output needed, then accordingly I can suggest you the solution.
Hi Vijay,
Hoping you can help me find out where I'm getting this wrong.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"POLICY_TYP", type text}, {"SCHEME FLAG", type text}, {"ANZSIC_SubDiv_Desc", type text}, {"ANZSIC_Group_Desc", type text}, {"ANZSIC_Code_Desc", type text}}),
ListValue = List.Buffer(SegmentationConditions[Value]),
ListSegment = List.Buffer(SegmentationConditions[Segmentation.1]),
Custom1 = Table.AddColumn(#"Changed Type", "Custom1",
each try ListSegment{List.PositionOf(ListValue,[ANZSIC_SubDiv_Desc])}
otherwise try ListSegment{List.PositionOf(ListValue,[ANZSIC_Code_Desc])}
otherwise try ListSegment{List.PositionOf(ListValue,[ANZSIC_Group_Desc])}
otherwise try ListSegment{List.PositionOf(ListValue,[SCHEME FLAG])}
otherwise try ListSegment{List.PositionOf(ListValue,[POLICY_TYP])}
otherwise try ListSegment{List.PositionOf(ListValue,[SCHEME NAME])}
otherwise null, Text.Type)
in
Custom1
let
Source = Excel.CurrentWorkbook(){[Name="SegmentationConditions"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-AU"),{"Index", "Segmentation"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Segmentation.1"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns", {"Segmentation.1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Value", Text.Upper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Uppercased Text","*","",Replacer.ReplaceValue,{"Value"})
in
#"Replaced Value"
Data:
POLICY_TYP | SCHEME FLAG | ANZSIC_SubDiv_Desc | ANZSIC_Group_Desc | ANZSIC_Code_Desc |
PID | FALSE | BUSINESS SERVICES | TECHNICAL SERVICES | ARCHITECTURAL SERVICES |
PID | TRUE | BUSINESS SERVICES | TECHNICAL SERVICES | ARCHITECTURAL SERVICES |
PID | FALSE | BUSINESS SERVICES | TECHNICAL SERVICES | ARCHITECTURAL SERVICES |
PID | FALSE | BUSINESS SERVICES | LEGAL AND ACCOUNTING SERVICES | ACCOUNTING SERVICES |
PID | TRUE | BUSINESS SERVICES | LEGAL AND ACCOUNTING SERVICES | ACCOUNTING SERVICES |
MNL | FALSE | CONSTRUCTION TRADE SERVICES | INSTALLATION TRADE SERVICES | PLUMBING SERVICES |
DOL | FALSE | CONSTRUCTION TRADE SERVICES | INSTALLATION TRADE SERVICES | PLUMBING SERVICES |
DOL | FALSE | AGRICULTURE | HORTICULTURE AND FRUIT GROWING | GRAPE GROWING |
PID | FALSE | HEALTH SERVICES | VETERINARY SERVICES | VETERINARY SERVICES |
ICT | FALSE | BUSINESS SERVICES | LEGAL AND ACCOUNTING SERVICES | COMPUTER CONSULTANCY SERVICES |
PID | FALSE | BUSINESS SERVICES | MARKETING AND BUSINESS MANAGEMENT SERVICES | BUSINESS MANAGEMENT SERVICES |
PID | TRUE | BUSINESS SERVICES | MARKETING AND BUSINESS MANAGEMENT SERVICES | BUSINESS MANAGEMENT SERVICES |
MNL | FALSE | BUSINESS SERVICES | MARKETING AND BUSINESS MANAGEMENT SERVICES | BUSINESS MANAGEMENT SERVICES |
MNL | FALSE | AGRICULTURE | HORTICULTURE AND FRUIT GROWING | FRUIT GROWING N.E.C. |
MNL | FALSE | FORESTRY AND LOGGING | FORESTRY AND LOGGING | LOGGING |
DOL | FALSE | FORESTRY AND LOGGING | FORESTRY AND LOGGING | LOGGING |
PID | FALSE | PROPERTY SERVICES | REAL ESTATE AGENTS | REAL ESTATE AGENTS |
PID | TRUE | PROPERTY SERVICES | REAL ESTATE AGENTS | REAL ESTATE AGENTS |
PID | FALSE | PROPERTY SERVICES | REAL ESTATE AGENTS | REAL ESTATE AGENTS |
MNL | FALSE | PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M | BASIC CHEMICAL MANUFACTURING | SYNTHETIC RESIN MANUFACTURING |
DOL | FALSE | PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M | BASIC CHEMICAL MANUFACTURING | SYNTHETIC RESIN MANUFACTURING |
DOL | FALSE | AGRICULTURE | GRAIN, SHEEP AND BEEF CATTLE FARMING | BEEF CATTLE FARMING |
Segments:
Segmentation | POLICY_TYP | SCHEME_FLAG | ANZSIC_INDUSTRY | ANZSIC_DIV |
Accountants Scheme | PID | TRUE | ACCOUNTING SERVICES | |
Management | MNL | FALSE | ||
Accountants Non-Scheme | FALSE | ACCOUNTING SERVICES | ||
Engineers | PID | TRUE | CONSULTING ENGINEERING SERVICES | |
Medical | MDI | FALSE | ||
Business Management Services - Non Scheme | FALSE | BUSINESS MANAGEMENT SERVICES | ||
Real Estate Agents - Rest | PID | FALSE | PROPERTY SERVICES |
The output is bringing up incorrect matches:
Note 2 - management for Product_typ = "DOL"
POLICY_TYP | SCHEME FLAG | ANZSIC_SubDiv_Desc | ANZSIC_Group_Desc | ANZSIC_Code_Desc | Custom1 |
PID | FALSE | BUSINESS SERVICES | TECHNICAL SERVICES | ARCHITECTURAL SERVICES | 2 - Management |
PID | TRUE | BUSINESS SERVICES | TECHNICAL SERVICES | ARCHITECTURAL SERVICES | 1 - Accountants Scheme |
PID | FALSE | BUSINESS SERVICES | TECHNICAL SERVICES | ARCHITECTURAL SERVICES | 2 - Management |
PID | FALSE | BUSINESS SERVICES | LEGAL AND ACCOUNTING SERVICES | ACCOUNTING SERVICES | 1 - Accountants Scheme |
PID | TRUE | BUSINESS SERVICES | LEGAL AND ACCOUNTING SERVICES | ACCOUNTING SERVICES | 1 - Accountants Scheme |
MNL | FALSE | CONSTRUCTION TRADE SERVICES | INSTALLATION TRADE SERVICES | PLUMBING SERVICES | 2 - Management |
DOL | FALSE | CONSTRUCTION TRADE SERVICES | INSTALLATION TRADE SERVICES | PLUMBING SERVICES | 2 - Management |
DOL | FALSE | AGRICULTURE | HORTICULTURE AND FRUIT GROWING | GRAPE GROWING | 2 - Management |
PID | FALSE | HEALTH SERVICES | VETERINARY SERVICES | VETERINARY SERVICES | 2 - Management |
ICT | FALSE | BUSINESS SERVICES | LEGAL AND ACCOUNTING SERVICES | COMPUTER CONSULTANCY SERVICES | 2 - Management |
PID | FALSE | BUSINESS SERVICES | MARKETING AND BUSINESS MANAGEMENT SERVICES | BUSINESS MANAGEMENT SERVICES | 6 - Business Management Services - Non Scheme |
PID | TRUE | BUSINESS SERVICES | MARKETING AND BUSINESS MANAGEMENT SERVICES | BUSINESS MANAGEMENT SERVICES | 6 - Business Management Services - Non Scheme |
MNL | FALSE | BUSINESS SERVICES | MARKETING AND BUSINESS MANAGEMENT SERVICES | BUSINESS MANAGEMENT SERVICES | 6 - Business Management Services - Non Scheme |
MNL | FALSE | AGRICULTURE | HORTICULTURE AND FRUIT GROWING | FRUIT GROWING N.E.C. | 2 - Management |
MNL | FALSE | FORESTRY AND LOGGING | FORESTRY AND LOGGING | LOGGING | 2 - Management |
DOL | FALSE | FORESTRY AND LOGGING | FORESTRY AND LOGGING | LOGGING | 2 - Management |
PID | FALSE | PROPERTY SERVICES | REAL ESTATE AGENTS | REAL ESTATE AGENTS | 7 - Real Estate Agents - Rest |
PID | TRUE | PROPERTY SERVICES | REAL ESTATE AGENTS | REAL ESTATE AGENTS | 7 - Real Estate Agents - Rest |
PID | FALSE | PROPERTY SERVICES | REAL ESTATE AGENTS | REAL ESTATE AGENTS | 7 - Real Estate Agents - Rest |
MNL | FALSE | PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M | BASIC CHEMICAL MANUFACTURING | SYNTHETIC RESIN MANUFACTURING | 2 - Management |
DOL | FALSE | PETROLEUM, COAL, CHEMICAL AND ASSOCIATED PRODUCT M | BASIC CHEMICAL MANUFACTURING | SYNTHETIC RESIN MANUFACTURING | 2 - Management |
DOL | FALSE | AGRICULTURE | GRAIN, SHEEP AND BEEF CATTLE FARMING | BEEF CATTLE FARMING | 2 - Management |
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
CODE FOR SOURCE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lHyyczPA1LOiUVKsToIYd/80uJUJHH/osS8dIhACZAMyEnMSwVLBKQCVcCEQ4oSM/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Animal = _t, Vehicle = _t]),
ListValue = List.Buffer(Conditions[Value]),
ListSegment = List.Buffer(Conditions[Segment]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each try ListSegment{List.PositionOf(ListValue,[Fruit])} otherwise try ListSegment{List.PositionOf(ListValue,[Animal])} otherwise try ListSegment{List.PositionOf(ListValue,[Vehicle])} otherwise null)
in
#"Added Custom"
CODE FOR CONDITIONS
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIi58QSIBmQk5iXqhSrE61kBOKlJhZBZMFCxsgqQ4oSM/PAwiZAnmNBQU4qklJTCNs3v7Q4FSZmBmT4FyXmpaeiWxcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Fruit = _t, Animal = _t, Vehicle = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", Int64.Type}, {"Fruit", type text}, {"Animal", type text}, {"Vehicle", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Segment"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> "" and [Value] <> null))
in
#"Filtered Rows"
Wow...
Give me an hour to figure out what you've done! I'll return and flag it as a solution, once I've verified it.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
18 | |
16 | |
11 |