Excel BI Challenge Workout 194

Workouts Excel BI Challenge
1

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 194 —
(Solutions in any language are also welcome for Excel Challenges)

Remove the characters preceding the asterisks. The number of characters to be removed will be equal to number of asterisks. If number of asterisks are more than number of characters on the left, then remove all characters on the left.
Nation => Ntn
Bill => ll

Post answers in Comment.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

Download Practice File - https://lnkd.in/dWgQU9PU

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

image
image800×475 105 KB

Excel BI’s LinkedIn Post

2

LinkedIn post by:
JvdV

‘=REDUCE(0,TEXTSPLIT(A2,“*”),LAMBDA(a,b,TRIM(LEFT(" "&a,LEN(a)))&b))’

Or, for the whole range at once:

=MAP(A2:A10,LAMBDA(c,REDUCE(0,TEXTSPLIT(c,"*"),LAMBDA(a,b,TRIM(LEFT(" "&a,LEN(a)))&b))))

Or, apply the same logic to PQ with M-code:

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
   #"Added Custom" = Table.AddColumn(#"Changed Type", "Answer", each List.Accumulate(Text.Split([String],"*"),"",(a,b)=> Text.Trim(Text.Start(" " & a, Text.Length(a)) & b)))
in
   #"Added Custom"
3

LinkedIn post by:
Stevenson Yu

This is so very ugly, but I finally got it to work. I had the most difficulty in counting the number of asterisks.

=MAP(A2:A10, LAMBDA(Q,
LET(A, Q,
B, TEXTSPLIT(A,"*"),
C, FILTER(B, B<>""),
D, LEN(C),
E, LOWER(REDUCE(A, REPT("*",SEQUENCE(LEN(A),,LEN(A),-1)), LAMBDA(X,Y, SUBSTITUTE(X, Y, LEN(Y))))),
F, REDUCE(E, CHAR(SEQUENCE(26,,97)), LAMBDA(X,Y, SUBSTITUTE(X, Y, "|"))),
G, TEXTSPLIT(F,"|"),
H, --EXPAND(FILTER(G, G<>""),1,COUNTA(C),0),
I, IF(H>D,0,D-H),
J, LEFT(C,I),
K, TEXTJOIN("",1,J), K)))
4

LinkedIn post by:
Alejandro Simón

tarde, pero aqui les adjunto mi query…

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Sol = Table.AddColumn(Source, "Answer", each 
    let
    a = Text.ToList([String]),
    b = List.Accumulate(a, {}, (s,c)=> if c <> "*" then s&{c} else try List.FirstN(s, List.Count(s)-1) otherwise {} ) 
    in Text.Combine(b, ""))[[Answer]]
in
  Sol
5

LinkedIn post by:
Lorenzo Foti
 

=+LET(
  str;MID(A2;SEQUENCE(LEN(A2));1);
  outP;REDUCE("";str;LAMBDA(a;v;IF(v="*";IFERROR(LEFT(a;LEN(a)-1);"");a&v)));
outP)
6

LinkedIn Post by:
Bo Rydobon

M-Code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformColumns(Source,{},each List.Accumulate({1..Text.Length([Text.Select](http://text.select/)(_,"*"))},_,(a,w)=> let x= Text.PositionOf(a,"*") in
Text.RemoveRange(a, List.Max({0,x-1}),List.Min({x+1,2} ))))
in
Ans
7

LinkedIn Post by:
Guillermo Arroyo

Power Query

let
  Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
  a = List.Transform(Origen[String], each 
    let 
      o = Text.ToList(_),
      r = (x,y) =>
        if x = List.Count(o)
        then y
        else @r(x+1, if o{x} = "*" then List.RemoveLastN(y) else List.Combine({y, {o{x}}}))
    in 
      r(0, {""})),
  b = List.Transform(a, each Text.Combine(_))
in
  b

image
image735×268 32.6 KB

8

LinkedIn post by:
Zoran Milokanović

Removing characters w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each Text.Combine(List.Accumulate(Text.ToList([String]), {}, (s, c) => if c = "*" then List.RemoveLastN(s) else s & {c}), ""))
in
Solution
9

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=MAP(A2:A10, LAMBDA(a, LET(_t, TEXTSPLIT(a, ,"*",1), CONCAT( SUBSTITUTE(_t, RIGHT(_t, TOCOL( LEN( TRIM( TEXTSPLIT(LOWER(a)&" ", CHAR( SEQUENCE(26, ,97)), ,1))))), "")))))

10

LinkedIn post by:
محمد حلمي
 

=MAP(A2:A10,LAMBDA(a,LET(r,TEXTSPLIT(a,"*",,1),l,
HSTACK(r,""),CONCAT(IFERROR(LEFT(l,LEN(l)-HSTACK(
LEN(TEXTSPLIT(UPPER(a),CHAR(ROW(65:90)),,1)),0)),"")))))
11

LinkedIn post by:
Bo Rydobon

=REDUCE("",SEQUENCE(50),LAMBDA(a,n,LET(b,MID(A2:A10,n,1),IF(b="*",LEFT(a,LEN(a)-(a>"")),a&b))))

12

LinkedIn Post by:
Bo Rydobon
 

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Ans = Table.TransformColumns(Source,{},each List.Accumulate(Text.ToList(_),"", (a,v)=> if v="*" then Text.Start(a, Text.Length(a)-Number.From(a>"")) else a&v))
in
 Ans
13

LinkedIn Post by:
John Jairo V.

Hi to all!

One option could be:

:white_check_mark:

=MAP(A2:A10,LAMBDA(x,REDUCE("",MID(x,ROW(1:30),1),LAMBDA(a,v,IF(v="*",LEFT(a,MAX(,LEN(a)-1)),a&v)))))

Blessings!

14

LinkedIn post by:
محمد حلمي
 

=MAP(A2:A10,LAMBDA(a,LET(l,HSTACK(TEXTSPLIT(a,"*",,1),""),CONCAT(IFERROR(LEFT(l,LEN(l)-HSTACK(LEN(
TEXTSPLIT(a,CHAR(ROW(65:122)),,1)),0)),"")))))
15

LinkedIn post by:
Bo Rydobon

=REDUCE(A2:A10,SEQUENCE(20),LAMBDA(a,w,LET(x,IFERROR(FIND("*",a)-1,99),REPLACE(a,IF(x,x,1),IF(x>1,2,1),))))

16

LinkedIn post by:
Hussein SATOUR

Thank you Excel BI

=MAP(A2:A10, LAMBDA(z, REDUCE(,MID(z,SEQUENCE(LEN(z)),1), LAMBDA(x,y, IF(y="*", IFERROR(LEFT(x, LEN(x)-1), x), x&y)))))
17

LinkedIn Post by:
Guillermo Arroyo
 

=MAP(A2:A10;LAMBDA(a;CONCAT(REDUCE("";MID(a;SEQUENCE(LEN(a));1);LAMBDA(i;j;IF(j="*";IFERROR(DROP(i;-1);"");VSTACK(i;j)))))))