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 Community,
I have a data set as shown below. Is there a way to have this done in power query editor. Column A and B is the Data input and Column C and D are the results expected.
Every help in this regard is very much appreciated. Thanks in advance
JP
Solved! Go to Solution.
Second column with countif added.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIGYkMwjtWBiMBEjcEipkCWKVTUCEXEBIzBugwMQCZYWqKYZQFiggmEXoQYVHcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C1 = _t, C2 = _t, C1Desired = _t, C2Desired = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"C1", Int64.Type}, {"C2", Int64.Type}, {"C1Desired", Int64.Type}, {"C2Desired", Int64.Type}}),
AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(AddedIndex, "SUMIF_1_argument",
each if [Index] = 0
then
{AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}
else
{AddedIndex{[Index]-1}[C1], AddedIndex{[Index]-1}[C2],
AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "SUMIF_2_argumentC1", each #"Added Custom"{[Index]}[C1]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ResultC1", (FirstIterator)=> List.Count( List.Select( FirstIterator[SUMIF_1_argument], each _ = FirstIterator[SUMIF_2_argumentC1]))),
//second column
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "SUMIF_2_argumentC2", each #"Added Custom"{[Index]}[C2]),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "ResultC2", (FirstIterator)=> List.Count( List.Select( FirstIterator[SUMIF_1_argument], each _ = FirstIterator[SUMIF_2_argumentC2])))
in
#"Added Custom4"
Nice challenge!
I have solved the first column. Number 2 is copy paste.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIGYkMwjtWBiMBEjcEipkCWKVTUCEXEBIzBugwMQCZYWqKYZQFiggmEXoQYVHcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C1 = _t, C2 = _t, C1D = _t, C2D = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"C1", Int64.Type}, {"C2", Int64.Type}, {"C1D", Int64.Type}, {"C2D", Int64.Type}}),
AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(AddedIndex, "SUMIF_1_argument",
each if [Index] = 0
then
{AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}
else
{AddedIndex{[Index]-1}[C1], AddedIndex{[Index]-1}[C2],
AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "SUMIF_2_argument", each #"Added Custom"{[Index]}[C1]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ResultC1", (FirstIterator)=> List.Count( List.Select( FirstIterator[SUMIF_1_argument], each _ = FirstIterator[SUMIF_2_argument])))
in
#"Added Custom2"
Hi @jpattamthanam ,
Great! How about column 2?Have you also achieved column 2?If so,could you pls mark the reply as answered to let more people find the solution?
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Second column with countif added.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIGYkMwjtWBiMBEjcEipkCWKVTUCEXEBIzBugwMQCZYWqKYZQFiggmEXoQYVHcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C1 = _t, C2 = _t, C1Desired = _t, C2Desired = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"C1", Int64.Type}, {"C2", Int64.Type}, {"C1Desired", Int64.Type}, {"C2Desired", Int64.Type}}),
AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(AddedIndex, "SUMIF_1_argument",
each if [Index] = 0
then
{AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}
else
{AddedIndex{[Index]-1}[C1], AddedIndex{[Index]-1}[C2],
AddedIndex{[Index]}[C1], AddedIndex{[Index]}[C2]}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "SUMIF_2_argumentC1", each #"Added Custom"{[Index]}[C1]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ResultC1", (FirstIterator)=> List.Count( List.Select( FirstIterator[SUMIF_1_argument], each _ = FirstIterator[SUMIF_2_argumentC1]))),
//second column
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "SUMIF_2_argumentC2", each #"Added Custom"{[Index]}[C2]),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "ResultC2", (FirstIterator)=> List.Count( List.Select( FirstIterator[SUMIF_1_argument], each _ = FirstIterator[SUMIF_2_argumentC2])))
in
#"Added Custom4"
Use "Group by" on column A, and choose "Count" as the metric, but click on "advanced" and add another metric, call this one "All" and select "All Rows" as the metric.
After the Group is complete, expand "All" to get your original rows back, but now with the Row Count column. Do the same for Column B and you are done.
But are the duplicates to be counted only those in the ranges of two rows and two columns? For example, the 3 in the second line comes from = COUNTIF ($ A2: $ B3; $ A3) the 1 of the sixth line comes from = COUNTIF ($ A5: $ B6, $ A6)
Ah! I get it.
You are right, my idea won't work.
I'm brainstorming what could work, but right now I've got nothing.
Their are a few functions that come to mind, maybe a combination of them:
//Table.AddIndexColumn() will add a column that you can use as a row reference.
AddRowCount = Table.AddIndexColumn(PreviousStep, "RowNumber", 1,1)
_List1 = AddRowCount[ChannelDWG1] // will create a list of the first column.
_List2 = AddRowCount[ChannelDWG2] // will create a list of the second column.
// List.FirstN(List, #) will reduce a list to that number of entries.
_List1Short = List.FirstN(_List1, each [RowNumber])
_List2Short = List.FirstN(_List2, each [RowNumber])
//List.Combine() will make them both one long list
_ListLong = List.Combine({_List1Short, _List2Short})
//List.Select() will filter the list down to just the entries you want
_ListSelect = List.Select(_ListLong, each [ChannelDWG1])
//List.Count() will count the rows in the list, aka how many rows in the combined list matched your criterea
_ListCount = List.Count(_ListSelect)
Something like that, correcting for the obvious formatting errors.
Something like this
AddRowCount = Table.AddIndexColumn(PreviousStep, "RowNumber", 1,1),
AddDWG1Dupes =
let
_List1 = List.Buffer(AddRowCount[ChannelDWG1]),
_List2 = List.Buffer(AddRowCount[ChannelDWG2]),
_List1Short = List.FirstN(_List1, each [RowNumber]),
_List2Short = List.FirstN(_List2, each [RowNumber]),
_ListLong = List.Combine({_List1Short, _List2Short}),
_ListSelect = List.Select(_ListLong, each [ChannelDWG1]),
_ListCount = List.Count(_ListSelect)
in
Table.AddColumn(AddRowCount, _ListCount),
AddDWG2Dupes =
let
_List1 = List.Buffer(AddRowCount[ChannelDWG1]),
_List2 = List.Buffer(AddRowCount[ChannelDWG2]),
_List1Short = List.FirstN(_List1, each [RowNumber]),
_List2Short = List.FirstN(_List2, each [RowNumber]),
_ListLong = List.Combine({_List1Short, _List2Short}),
_ListSelect = List.Select(_ListLong, each [ChannelDWG2]),
_ListCount = List.Count(_ListSelect)
in
Table.AddColumn(AddDWG2Dupes, _ListCount),
Hi, @jpattamthanam , in general, you may transform the target region (eg A1:B2 in your example) to list and use
List.Count(List.PositionOf( list , "substring" , Occurrence.All))
to count the occurrence of a specific string.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 |