Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |