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.
Dear, I have a sample dataset as shown below, could you please instruct me on how to add a custom column to flag the first occurrence of the product type (as 1, else as 0) in the Power Query environment? The [flag first occurrence of the product type] column below is the expected outcome.
Thank you in advance!
Product | XXX | XXX | XXX | [flag first occurrence of the product type] |
A | XXX | XXX | XXX | 1 |
A | XXX | XXX | XXX | 0 |
A | XXX | XXX | XXX | 0 |
B | XXX | XXX | XXX | 1 |
C | XXX | XXX | XXX | 1 |
C | XXX | XXX | XXX | 0 |
D | XXX | XXX | XXX | 1 |
D | XXX | XXX | XXX | 0 |
B | XXX | XXX | XXX | 0 |
C | XXX | XXX | XXX | 0 |
D | XXX | XXX | XXX | 0 |
Solved! Go to Solution.
This can help.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqIiEAhY3VIF3fCIe5MorgLieKk2ovVnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, XXX = _t, XXX.1 = _t, XXX.2 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.PositionOf( Source[Product], [Product]) ),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Flag", each Number.From([Custom]=[Index])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
#"Removed Columns"
is it possible to get the last occurence flag using any similar method
I have a similar query; I am trying to flag the first occurrence of a combination of values from two different columns.
Thanks for any guidance!
Product | Month | First Occurrence |
A | 1 | 1 |
A | 1 | 0 |
A | 2 | 1 |
B | 1 | 1 |
C | 1 | 1 |
C | 2 | 1 |
D | 1 | 1 |
D | 1 | 0 |
B | 1 | 0 |
C | 2 | 0 |
D | 2 | 1 |
Try the following code with replacing the Source step with your query.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ToText = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Month", type text}}),
List = List.Transform( List.Zip ( {ToText[Product], ToText[Month]} ), Text.Combine),
#"Added Custom" = Table.AddColumn(ToText, "Custom", each List.PositionOf( List, [Product]&[Month])),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "First Occurrence", each Number.From( [Custom]=[Index] )),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
#"Removed Columns"
Result
Hi @WencyREN ,
Please firstly try @Jakinta 's method.
My workaround is adding Index column and using "Group by":
You could create a blank query and paste the following M syntax to Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqIiEAhY3VIF3fCIe5MorgLieKk2ovVnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, A = _t, B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"A", type text}, {"B", type text}, {"C", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Product"}, {{"Count", each _, type table [Product=nullable text, A=nullable text, B=nullable text, C=nullable text, Index=number]}, {"Min", each List.Min([Index]), type number}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"A", "B", "C", "Index"}, {"A", "B", "C", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Index]=[Min] then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Min"})
in
#"Removed Columns"
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This can help.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYqIiEAhY3VIF3fCIe5MorgLieKk2ovVnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, XXX = _t, XXX.1 = _t, XXX.2 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.PositionOf( Source[Product], [Product]) ),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Flag", each Number.From([Custom]=[Index])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
#"Removed Columns"
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 | |
48 | |
19 | |
13 | |
11 |