Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KuntalSingh
Helper V
Helper V

Need help on Index match and formula IFERROR(IF(SUMIFS(M:M,V:V,V2)

‎09-03-2024 06:13 AM

 

=IFERROR(IF(SUMIFS(Amountindoccurr:Amountindoccurr,Remove Alphapets:Remove Alphapets,Remove Alphapets2)>ABS(INDEX(Reference:Remove Alphapets,MATCH(Remove Alphapets2,Remove Alphapets:Remove Alphapets,0),1)),"High Priority",""),"")

 

excel formula is  =IFERROR(IF(ABS(SUMIFS(M:M,U:U,U2))>ABS(INDEX(M:U,MATCH(U2,U:U,0),1)),"High Priority",""),"") I need to apply same formula in Power query

 

ReferenceAmountindoccurrColumn1Remove AlphapetsSumif AmountAmountcopy
128404JAIMES-5400 1284045400 5400
128404JAIMES5400 1284045400 5400
70015877987975.5 1284047975.5High Priority7975.5
70015877987975.5 1284047975.5High Priority7975.5
70015877987975.5 1284047975.5High Priority7975.5
11332716-5018.4 1284045018.4 5018.4
11332716-5018.4 1284045018.4 5018.4
 -10036.8 12840410036.8 10036.8
Labels:
Message 1 of 15
506 Views
0
1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

‎09-05-2024 12:41 AM

Hi @KuntalSingh 

Thanks for your reply, you can try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMDEw8XL09HUNVtJR0jU1MTAA0hBhKAdMxepgKMar1tzAwNDUwtzc0gIoam5pbqpnClcL50IZ9FBvaGhsbGRuaAb2pIGhhZ4JwukwLpRBlnIjU2MTqI1ACoTQxc1g4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Amountindoccurr = _t, #"Remove Alphapets" = _t, Amountcopy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Amountindoccurr", type number}, {"Remove Alphapets", type number}, {"Amountcopy", type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Amountindoccurr", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type1",{{"Amountindoccurr",each if Text.Contains(_,"-") then _ else "+"&_ }}),
    #"Grouped Rows" = Table.Group(Custom1, {"Reference"}, {{"Data", each Text.Combine([Amountindoccurr],",")},{"Tables", each _, type table [Reference=nullable text, Amountindoccurr=text, Remove Alphapets=nullable number, Amountcopy=nullable number]},{"Counts", each Table.RowCount(_)}}),
    #"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables", {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}, {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Tables", "Custom", each let a=Text.Split([Data],","),
b=[Amountindoccurr],
c=List.Count(List.Select(a, each Text.Contains(_,b))),
d=if Text.Contains([Data],"-") and Text.Contains([Data],"+") then null else if [Counts]=c then "High Priority" else null
in d),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Amountindoccurr", Int64.Type}})
in
    #"Changed Type2"

Output

vxinruzhumsft_0-1725521777018.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Need help on Index match.pbix
Message 15 of 15
280 Views
0
14 REPLIES 14
v-xinruzhu-msft
Community Support
Community Support

‎09-05-2024 12:41 AM

Hi @KuntalSingh 

Thanks for your reply, you can try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMDEw8XL09HUNVtJR0jU1MTAA0hBhKAdMxepgKMar1tzAwNDUwtzc0gIoam5pbqpnClcL50IZ9FBvaGhsbGRuaAb2pIGhhZ4JwukwLpRBlnIjU2MTqI1ACoTQxc1g4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Amountindoccurr = _t, #"Remove Alphapets" = _t, Amountcopy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Amountindoccurr", type number}, {"Remove Alphapets", type number}, {"Amountcopy", type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Amountindoccurr", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type1",{{"Amountindoccurr",each if Text.Contains(_,"-") then _ else "+"&_ }}),
    #"Grouped Rows" = Table.Group(Custom1, {"Reference"}, {{"Data", each Text.Combine([Amountindoccurr],",")},{"Tables", each _, type table [Reference=nullable text, Amountindoccurr=text, Remove Alphapets=nullable number, Amountcopy=nullable number]},{"Counts", each Table.RowCount(_)}}),
    #"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables", {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}, {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Tables", "Custom", each let a=Text.Split([Data],","),
b=[Amountindoccurr],
c=List.Count(List.Select(a, each Text.Contains(_,b))),
d=if Text.Contains([Data],"-") and Text.Contains([Data],"+") then null else if [Counts]=c then "High Priority" else null
in d),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Amountindoccurr", Int64.Type}})
in
    #"Changed Type2"

Output

vxinruzhumsft_0-1725521777018.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Need help on Index match.pbix
Message 15 of 15
281 Views
0
v-xinruzhu-msft
Community Support
Community Support

‎09-03-2024 07:37 PM

Hi @KuntalSingh ，

Based on your formula

INDEX(Reference:Remove Alphapets,MATCH(Remove Alphapets2,Remove Alphapets:Remove Alphapets,0),1))

it will return the "128404JAIMES", is it wrong?

 

Best Regards!

Yolo Zhu

Message 6 of 15
415 Views
0
KuntalSingh
Helper V
Helper V
In response to v-xinruzhu-msft

‎09-03-2024 07:44 PM

I need 

 same reference number have + & - value in column Amountindoccurr then comment is null and  same reference column have ++ or -- value in  Amountindoccurr then comment is hight priority

Message 14 of 15
408 Views
0
KuntalSingh
Helper V
Helper V
In response to v-xinruzhu-msft

‎09-03-2024 07:42 PM

I need 

 same reference number have + & - value in column Amountindoccurr then comment is null and  same reference column have ++ or -- value in  Amountindoccurr then comment is hight priority

Message 7 of 15
409 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to KuntalSingh

‎09-03-2024 07:55 PM

Hi @KuntalSingh 

Thanks for your quick reply ,based on your description, is the 11332716 be high priority, it has "--" value

 

Best Regards!

Yolo Zhu

Message 8 of 15
393 Views
0
KuntalSingh
Helper V
Helper V
In response to v-xinruzhu-msft

‎09-03-2024 08:04 PM

Yes please

Message 9 of 15
384 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to KuntalSingh

‎09-03-2024 08:42 PM

Hi @KuntalSingh 

You can create a blank query and put the following code to advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMDEw8XL09HUNVtJR0jU1MTAA0hBhKAdMxepgKMar1tzAwNDUwtzc0gIoam5pbqpnClcL50IZ9FBvaGhsbGRuaAb2pIGhhZ4JwukwLpRBqvJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Amountindoccurr = _t, #"Remove Alphapets" = _t, Amountcopy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Amountindoccurr", type number},  {"Remove Alphapets", type number}, {"Amountcopy", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=[Reference],
b=Table.SelectRows(#"Changed Type",each [Reference]=a)[Amountindoccurr],
c=List.Count(List.Select(b,each _>0)),
d=List.Count(List.Select(b,each _<0)),
e=List.Count(b)
in if c=e or d=e then "High Priority" else null)
in
    #"Added Custom"

Output

vxinruzhumsft_0-1725421233001.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Need help on Index match.pbix
Message 10 of 15
377 Views
0
KuntalSingh
Helper V
Helper V
In response to v-xinruzhu-msft

‎09-03-2024 09:59 PM

I used above mention code it keeps on loading from last 20 mins also data size increase from MB to GB. Please help to optimised the loading time as we have more than 60K rows 

Message 11 of 15
360 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to KuntalSingh

‎09-04-2024 12:24 AM

Hi @KuntalSingh 

You can try the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMDEw8XL09HUNVtJR0jU1MTAA0hBhKAdMxepgKMar1tzAwNDUwtzc0gIoam5pbqpnClcL50IZ9FBvaGhsbGRuaAb2pIGhhZ4JwukwLpRBqvJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Amountindoccurr = _t, #"Remove Alphapets" = _t, Amountcopy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Amountindoccurr", type number}, {"Remove Alphapets", type number}, {"Amountcopy", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Reference"}, {{"Count>0", each List.Count(List.Select([Amountindoccurr],each _>0))}, {"Count<0", each List.Count(List.Select([Amountindoccurr],each _<0))},{"Count_rows", each Table.RowCount(_), Int64.Type}, {"Data", each _, type table [Reference=nullable text, Amountindoccurr=nullable number, Remove Alphapets=nullable number, Amountcopy=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}, {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each if [#"Count>0"]=[Count_rows] or [#"Count<0"]=[Count_rows] then "High Priority" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count>0", "Count<0", "Count_rows"})
in
    #"Removed Columns"let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyMDEw8XL09HUNVtJR0jU1MTAA0hBhKAdMxepgKMar1tzAwNDUwtzc0gIoam5pbqpnClcL50IZ9FBvaGhsbGRuaAb2pIGhhZ4JwukwLpRBqvJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Amountindoccurr = _t, #"Remove Alphapets" = _t, Amountcopy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Amountindoccurr", type number}, {"Remove Alphapets", type number}, {"Amountcopy", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Reference"}, {{"Count>0", each List.Count(List.Select([Amountindoccurr],each _>0))}, {"Count<0", each List.Count(List.Select([Amountindoccurr],each _<0))},{"Count_rows", each Table.RowCount(_), Int64.Type}, {"Data", each _, type table [Reference=nullable text, Amountindoccurr=nullable number, Remove Alphapets=nullable number, Amountcopy=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}, {"Amountindoccurr", "Remove Alphapets", "Amountcopy"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each if [#"Count>0"]=[Count_rows] or [#"Count<0"]=[Count_rows] then "High Priority" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count>0", "Count<0", "Count_rows"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Need help on Index match.pbix
Message 12 of 15
341 Views
0
KuntalSingh
Helper V
Helper V
In response to v-xinruzhu-msft

‎09-04-2024 11:03 PM

Please help on one more logic

same number in CC column have + and -

value in Amountindoccurr column means nullyfi then comment is blank and same number in CC column have  same  value either + or - in Amountindoccurr column then comment is Highy priority

 

Excel formula

1.Apply formula in  Column CC to remove alphabets from Reference(L Column) AND Concatenate with ABS(amt docu currency) after Please implement your logic to get the output

 

 

ScriptsVendorVendorNameBusinessAreaCompanyCodeFiscalYearDocumentTypeDocumentDatePostingKeyPostingDateDocumentNumberReferenceAmountindoccurrDocumentCurrencyAmountinlocalcurrLocalCurrencyClearingDocumentClearingDateTextNetDueDateConcatenationCC
S(25-60)-12004235596G&G OUTFITTERS INCUS052024RE4533131455305.1E+09242772-166.75USD-166.75USD   4/9/202451001530992024US05242772166.75
S(25-60)-12000011986G&G OUTFITTERS INCUS052024RE4533131453385.1E+09242772-166.75USD-166.75USD20005467724/14/20244/9/202451000427342024US05242772166.75
S(25-60)-12003871958CINTAS CORPORATION NUS012024RE4529331455305.1E+094178789859-205.94USD-205.94USD   3/2/202451018370002024US014178789859205.94
S(25-60)-12000029014CINTAS CORPORATION 100US012024RE4529331455105.1E+094178789859-205.94USD-205.94USD   3/2/202451005247402024US014178789859205.94
S(25-60)-12003871958CINTAS CORPORATION NUS012024RE4530731455305.1E+094180383750-209.39USD-209.39USD   3/16/202451000625092024US014180383750209.39
S(25-60)-12000011956CINTAS CORPORATION NO 2US012024RE4530731455065.1E+094180383750-209.39USD-209.39USD   3/16/202451009005242024US014180383750209.39
S(25-60)-22000014020NYSCO PRODUCTS LLCUS052024RE4459221455305.1E+090236725-IN91.4USD91.4USD  VR:Full Accrual4/1/202251008240022024US050236725-IN91.4
S(25-60)-22000014020NYSCO PRODUCTS LLCUS052024RE4459231454575.1E+090236725-IN-91.4USD-91.4USD    51010235452024US050236725-IN91.4
S(25-60)-22000022228W W GRAINGERUS012024RE4500721455305.1E+099648709799217.97USD217.97USD  VR:Pay-as-billed7/20/202351003365112024US019648709799217.97
S(25-60)-22000022228W W GRAINGERUS012024RE4500731454225.1E+099648709799-217.97USD-217.97USD  VR:Pay-as-billed7/20/202351012950142024US019648709799217.97
S(25-60)-22004015811CINTAS CORPORATION NUS052024RE4528131455305.1E+09I312210921-234.72USD-234.72USD  VR:Pay-as-billed2/19/202451024855002024US05I312210921234.72
S(25-60)-22000031331CINTAS FIRE PROTECTIONUS242024RE4528121453205.1E+09I312210921234.72USD234.72USD1008315074/3/2024VR:Pay-as-billed2/19/202451000080072024US24I312210921234.72
S(25-60)-22000031331CINTAS FIRE PROTECTIONUS242024RE4528131452995.1E+09I312210921-234.72USD-234.72USD1008315074/3/2024VR:Pay-as-billed2/19/202451000540022024US24I312210921234.72
S(25-60)-22000022228W W GRAINGERUS012024RE4528131455305.1E+099943375106-207.97USD-207.97USD   4/19/202451003750022024US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012023RE4528131452885.1E+099943375106-207.97USD-207.97USD   4/19/202451013625832023US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012024RE4528131454575.1E+099943375106-207.97USD-207.97USD   4/19/202451011440152024US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012024RE4528121454885.1E+099943375106207.97USD207.97USD  MR8M4/19/202451005320102024US019943375106207.97
S(25-60)-22000022228W W GRAINGERUS012024RE4528121454275.1E+099943375106207.97USD207.97USD  MR8M4/19/202451006805052024US019943375106207.97
S(25-60)-22003871958CINTAS CORPORATION NUS012024RE4530731455305.1E+094180383750-209.39USD-209.39USD   3/16/202451000625092024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530731455065.1E+094180383750-209.39USD-209.39USD   3/16/202451009005242024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530721455075.1E+094180383750209.39USD209.39USD  MR8M3/16/202451014410102024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530731455075.1E+094180383750-209.39USD-209.39USD20001180108/4/2024 3/16/202451005250672024US014180383750209.39
S(25-60)-22000011956CINTAS CORPORATION NO 2US012024RE4530721455075.1E+094180383750209.39USD209.39USD20001180108/4/2024MR8M3/16/202451008885072024US014180383750209.39
S(25-60)-22000029014CINTAS CORPORATION 100US012024RE4530731455105.1E+094180383750-209.39USD-209.39USD   3/16/202451004805942024US014180383750209.39
Message 13 of 15
295 Views
0
KuntalSingh
Helper V
Helper V

‎09-03-2024 04:42 PM

Column M is reference and column U is Remove Alphapets

Message 3 of 15
439 Views
0
Omid_Motamedise
Impactful Individual
Impactful Individual
In response to KuntalSingh

‎09-03-2024 06:11 PM

is the result of INDEX(M:U,MATCH(U2,U:U,0),1)) equal to the result of n2?

Message 4 of 15
428 Views
KuntalSingh
Helper V
Helper V
In response to Omid_Motamedise

‎09-03-2024 07:26 PM

same reference number have + & - value in column Amountindoccurr then comment is null and  same reference column have ++ or -- value in  Amountindoccurr then comment is hight priority 

Message 5 of 15
421 Views
0
Omid_Motamedise
Impactful Individual
Impactful Individual

‎09-03-2024 03:17 PM

it is not clear which column of your table refer to column M:M or U:U in Excel

Message 2 of 15
460 Views

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All