Excel BI Challenge Workout 194

— 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
Bi
ll => 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

Excel BI’s LinkedIn Post

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"

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)))

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

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)

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

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

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

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))))), "")))))

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)),"")))))

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))))

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

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!

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)),"")))))

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),))))

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)))))

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)))))))