Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
KuntalSingh
Helper V
Helper V

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

 

=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
1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

14 REPLIES 14
v-xinruzhu-msft
Community Support
Community Support

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.

v-xinruzhu-msft
Community Support
Community Support

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

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

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

Hi @KuntalSingh 

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

 

Best Regards!

Yolo Zhu

Yes please

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.

 

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 

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.

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
KuntalSingh
Helper V
Helper V

Column M is reference and column U is Remove Alphapets

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

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 

Omid_Motamedise
Memorable Member
Memorable Member

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.