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.
Hello to all,
I´m very new to Power Query, Power BI and M language.
May someone please give me some help. I´d like to know how to add a custom column that increments each time that column MONTH = "February" or "September"
I´m not sure if it is possible to add a custom fuction something like this:
i=0 if [MONTH] = "February" or [MONTH] = "September" then [CUSTOM] = i + 1 else [CUSTOM] = null
To get an output like this in column CUSTOM
MONTH | CUSTOM |
March | |
February | 1 |
June | |
June | |
July | |
August | |
September | 2 |
January | |
September | 3 |
February | 4 |
November | |
November | |
December | |
September | 5 |
December | |
January | |
February | 6 |
March |
Thanks in advance for any help.
Solved! Go to Solution.
Please see if this is useful
File attached as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUvBNLErOUFDSUVJQitUB8t1Sk4pKE4sqQUKGECGv0rxUJBUY3JxKJK5jaXppcQmSQHBqQUlqblJqEUjMCKonMQ9mBzZVxphOMYEI+eWXwVUp4BRySU1GF0Ix3xSnMkyHITvCDCIEDzKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MONTH = _t, CUSTOM = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"CUSTOM"}), AddedIndex = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(AddedIndex, each ([MONTH] = " February " or [MONTH] = " September ")), AddedIndex1 = Table.AddIndexColumn(#"Filtered Rows", "Custom", 1, 1), #"Merged Queries" = Table.NestedJoin(AddedIndex,{"MONTH", "Index"},AddedIndex1,{"MONTH", "Index"},"AddedIndex1",JoinKind.LeftOuter), #"Expanded AddedIndex1" = Table.ExpandTableColumn(#"Merged Queries", "AddedIndex1", {"Custom"}, {"Custom"}), #"Sorted Rows" = Table.Sort(#"Expanded AddedIndex1",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns1"
Hi @cgkas
I dont think List.Generate would be efficient in this case but here are 2 custom column formulas(Both use Index Column as support). The second one uses List.Generate
Please see table2 in attached file's Query Editor
=if [MONTH] ="February" or [MONTH] = "September" then let myindex=[Index] in Table.RowCount (Table.SelectRows(AddedIndex,each [Index] <= myindex and ([MONTH] ="February" or [MONTH] = "September"))) else null
=let myindex=[Index] in if [MONTH] ="February" or [MONTH] = "September" then List.Count(List.Select(List.Generate(()=>[x=0,y=null], each [x]<myindex ,each [x=[x]+1,y=AddedIndex[MONTH]{x}], each [y]),each List.Contains({"February","September"},_))) else null
Please see if this is useful
File attached as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUvBNLErOUFDSUVJQitUB8t1Sk4pKE4sqQUKGECGv0rxUJBUY3JxKJK5jaXppcQmSQHBqQUlqblJqEUjMCKonMQ9mBzZVxphOMYEI+eWXwVUp4BRySU1GF0Ix3xSnMkyHITvCDCIEDzKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MONTH = _t, CUSTOM = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"CUSTOM"}), AddedIndex = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(AddedIndex, each ([MONTH] = " February " or [MONTH] = " September ")), AddedIndex1 = Table.AddIndexColumn(#"Filtered Rows", "Custom", 1, 1), #"Merged Queries" = Table.NestedJoin(AddedIndex,{"MONTH", "Index"},AddedIndex1,{"MONTH", "Index"},"AddedIndex1",JoinKind.LeftOuter), #"Expanded AddedIndex1" = Table.ExpandTableColumn(#"Merged Queries", "AddedIndex1", {"Custom"}, {"Custom"}), #"Sorted Rows" = Table.Sort(#"Expanded AddedIndex1",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns1"
Hello Zubair,
Thanks for answer. It seems to work pretty fine.
Even it works and I could accept it as answer I´d like to know how to do it in form like for loop.
something like this example I took from here
fnIterateNestedLoop = (RowCount, ColumnCount, fnTransformByIndices) => List.Generate( ()=>[i=0, j=0], each [i] < RowCount, each if [j] < ColumnCount - 1 then [i=[i], j=[j]+1] else [i=[i]+1, j=0], each fnTransformByIndices([i], [j]) ),
Best regards
Hi @cgkas
I dont think List.Generate would be efficient in this case but here are 2 custom column formulas(Both use Index Column as support). The second one uses List.Generate
Please see table2 in attached file's Query Editor
=if [MONTH] ="February" or [MONTH] = "September" then let myindex=[Index] in Table.RowCount (Table.SelectRows(AddedIndex,each [Index] <= myindex and ([MONTH] ="February" or [MONTH] = "September"))) else null
=let myindex=[Index] in if [MONTH] ="February" or [MONTH] = "September" then List.Count(List.Select(List.Generate(()=>[x=0,y=null], each [x]<myindex ,each [x=[x]+1,y=AddedIndex[MONTH]{x}], each [y]),each List.Contains({"February","September"},_))) else null
Hi Zubair,
Excellent. Thanks so much for the examples shared in both custom formulas. Then I think first custom formula would be fast that List.Generate() for what you says. But between your first solution and your solution with first custom formula, which would be faster?
Thanks so much fot the help
Custom Column is simpler and should be faster
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 |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |