Excel BI Power Query Challenge Workout 077

-CAN YOU SOLVE THIS - POWER QUERY CHALLENGE 77-
(Solution in any language also welcome for Power Query Challenges)

Generate the result table from problem table. Quantity in result table is sum of number appearing next to the letter in problem table.

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

(Post answers in Comment. Your approach need not be different from others as long as you have worked out your approach independently)

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

LinkedIn Post:

LinkedIn Post by:
Alejandra Horvath CPA, CGA

My PQ solution:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split = Table.TransformRows (Source, each
let
x = Text.Replace([String], " ","" ),
y = Splitter.SplitTextByCharacterTransition((a) => not List.Contains({"A".."Z"}, a), {"A".."Z"})(x)
in
y),
ToTbl = Table.FromList(Split, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expanded = Table.ExpandListColumn(ToTbl, "Column1"),
SplitByCharTransition = Table.SplitColumn(Expanded, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Quantity"}),
ChgType = Table.TransformColumnTypes(SplitByCharTransition,{{"Quantity", Int64.Type}}),
Grouped = [Table.Group](http://table.group/)(ChgType, {"Letters"}, {{"Quantity", each List.Sum([Quantity]), type nullable text}}),
Sorted = Table.Sort(Grouped,{{"Letters", Order.Ascending}})
in
Sorted

Cheers :blush:

LinkedIn Post by:
Krzysztof Nowak

My solution in #R
Answer <- data %>% 
 mutate(String2 = str_replace_all(String,"(?<=[A-Z])\\s","")) %>% #remove all spaces followed by uppercase letter 
 unnest_tokens(input = String2,output = Components,token = "regex",pattern = "\\s",drop = F,to_lower = F) %>% #Split to rows using space as delimiter
 mutate(Letter = str_extract(Components,"[A-Z]"), # extract letters
     Digits = as.numeric(str_extract(Components,"\\d+"))) %>% #extract numbers
 group_by(Letter) %>% #Group by letter
 summarise(Quantity = sum(Digits)) #sum digits

LinkedIn Post by:
Alejandro Simón

Adjunto mi query...
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Col = Table.Combine(Table.AddColumn(Source, "Custom", each 
    let
    a = Text.Replace([String], " ", ""),
    b = Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z"})(a),
    c = List.Transform(b, each {Text.ToList(_){0}} & {Text.Combine(List.Skip(Text.ToList(_)))}),
    d = Table.FromRows(c)
    in d)[Custom]),
  Group = Table.Sort(Table.Group(Col, {"Column1"}, {{"Quality", each 
let
    a = [Column2],
    b = List.Sum (List.Transform(a, Number.From))
    in b
}}), "Column1"),
  Sol = Table.RenameColumns(Group,{{"Column1", "Letters"}})
in
  Sol

LinkedIn Post by:
Bo Rydobon

M-Code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = let z = Text.Combine(Source[String]), a =[List.Select](http://list.select/)(Text.SplitAny(z,"01234568789 "),each _>"") in
Table.Sort([Table.Group](http://table.group/)(Table.FromColumns({a , List.Transform([List.Select](http://list.select/)(Splitter.SplitTextByAnyDelimiter(a)(z),each _>""),Number.From)},{"Letters","Q"}),"Letters",{"Quantity", each List.Sum([Q])}),"Letters")
in
Ans

LinkedIn Post by:
Brian Julius

The rare #powerquery :

100% UI solution
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Duplicated Column" = Table.DuplicateColumn(Source, "String", "Anchor"),
  RemoveSpaces = Table.ReplaceValue(#"Duplicated Column"," ","",Replacer.ReplaceText,{"String"}),
  #"Split Column by Character Transition" = Table.SplitColumn(RemoveSpaces, "String", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"String.1", "String.2", "String.3", "String.4", "String.5"}),
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Character Transition", {"Anchor"}, "Attribute", "Value"),
  #"Split Column by Character Transition1" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letter", "Quantity"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition1",{{"Quantity", Int64.Type}}),
  #"Grouped Rows" = Table.Group(#"Changed Type", {"Letter"}, {{"Sum", each List.Sum([Quantity]), type nullable number}}),
  #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Letter", Order.Ascending}})
in
  #"Sorted Rows"

LinkedIn Post by:
Mahmoud Bani Asadi

PQ solution:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SplitColumnbyDelimiter = Table.ExpandListColumn(Table.TransformColumns(Source, {{"String", Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z", " "}), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "String"),
SplitColumnbyCharacterTransition = Table.SplitColumn(SplitColumnbyDelimiter, "String", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Quantity"}),
TrimmedText = Table.TransformColumns(SplitColumnbyCharacterTransition,{{"Letters", Text.Trim},{"Quantity", Number.From}}),
GroupedRows = [Table.Group](http://table.group/)(TrimmedText, {"Letters"}, {{"Qty", each List.Sum([Quantity]), type nullable text}}),
SortedRows = Table.Sort(GroupedRows,{{"Letters", Order.Ascending}})
in
SortedRows

LinkedIn Post by:
Eric Laforce

PQ Proposal with following 4 MainSteps
(1) Transform = split by CharacterTransition into Records
(2) Expand Records (3) Group by Letter with Sumof Quantity
(4) Sort

let
Source = Excel.CurrentWorkbook(){[Name="tData77"]}[Content],
Transform = Table.TransformColumns(Source, {"String", each let
_S = Text.Replace(_, " ", ""),
_Split=Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z"})(_S)
in List.Transform(_Split, each
Record.FromList( Splitter.SplitTextByCharacterTransition( {"A".."Z"},{"0".."9"})(_), {"Letters", "Value"})) }),
Expand = Table.ExpandRecordColumn( Table.ExpandListColumn(Transform, "String"), "String", {"Letters", "Value"}),
ChangeType = Table.TransformColumnTypes(Expand,{{"Value", Int64.Type}}),
Group = [Table.Group](http://table.group/)(ChangeType, {"Letters"}, {{"Quantity", each List.Sum([Value])}}),
Sort = Table.Sort(Group,{{"Letters", Order.Ascending}})
in Sort

LinkedIn Post by:
Taeyong Shin

let
  Source = Excel.CurrentWorkbook(){[ Name = "tblData" ]}[Content], 
  ReplaceValue = Table.ReplaceValue ( Source, 
    each [String], 
    each List.Accumulate ( { "0" .. "9" }, 
      [String], 
      ( a, c ) => Text.Replace ( a, c & " ", c & ", " )
    ), 
    Replacer.ReplaceText, 
    { "String" }
  ), 
  Split = Table.ExpandListColumn (
    Table.TransformColumns ( ReplaceValue, { "String", Splitter.SplitTextByDelimiter ( ", " ) } ), 
    "String"
  ), 
  AddCol = Table.AddColumn ( Split, "Letter", each Text.Start ( [String], 1 ), type text ), 
  Group = Table.Group ( AddCol, "Letter", 
    { "Quantity", 
      each List.Sum (
        List.Transform ( [String], each Number.From ( Text.RemoveRange ( _, 0, 1 ) ) )
      ), 
      Int64.Type
    }
  ), 
  SortTbl = Table.Sort ( Group, { "Letter", 0 } )
in
  SortTbl

LinkedIn Post by:
Omid Motamedisedeh

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = [A=Source[String],B=List.Combine(List.Transform(A,each Text.Split(Text.Remove(_,{"A".."Z"})," "))),C=List.Combine(List.Transform(A,each Text.Split([Text.Select](http://text.select/)(_,{"A".."Z"," "})," "))),D=Table.TransformColumnTypes(Table.FromColumns({[List.Select](http://list.select/)(C, each _<>""),[List.Select](http://list.select/)(B, each _<>"")},{"Letters","Quantity"}),{{"Quantity", Int64.Type}})][D],
#"Grouped Rows" = [Table.Group](http://table.group/)(Custom1, {"Letters"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Letters", Order.Ascending}})
in
#"Sorted Rows"

LinkedIn Post by:
Luan Rodrigues

let
Fonte = Tabela1,
add = Table.AddColumn(Fonte, "lista", each [
a = Text.Replace([String]," ",""),
b = Splitter.SplitTextByCharacterTransition({"0".."9"},{"A".."Z"})(a),
c = List.Combine({b})
][c])[[lista]],
exp = Table.ExpandListColumn(add, "lista"),
div = Table.SplitColumn(exp, "lista", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Quantity"}),
gp = [Table.Group](http://table.group/)(div, {"Letters"}, {{"Quantity", each List.Sum(List.Transform([Quantity], Number.From))}}),
res = Table.Sort(gp,{{"Letters", Order.Ascending}})
in
res

LinkedIn Post by:
Victor Wang

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split1 = Table.FromValue(Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z", " "})(Text.Combine(Source[String]))),
Split2 = Table.SplitColumn(Split1, "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Quantity"}),
Transform = Table.TransformColumns(Split2, {{"Letters", Text.Trim}, {"Quantity", Number.From}}),
Result = Table.Sort([Table.Group](http://table.group/)(Transform, {"Letters"}, {{"Quantity", each List.Sum([Quantity]), type nullable number}}), "Letters")
in
Result

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByCharacterTransition(
{"0".."9"}, (c) => not List.Contains({"0".."9"}, c))([String])),
 #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
 #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom"," ","",Replacer.ReplaceText,{"Custom"}),
 #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Custom", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Custom.2"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Custom.2", Int64.Type}}),
 #"Grouped Rows" = Table.Group(#"Changed Type1", {"Letters"}, {{"Quantity", each List.Sum([Custom.2]), type nullable text}}),
 #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Letters", Order.Ascending}})
in
 #"Sorted Rows"

LinkedIn Post by:
Zoran Milokanović

Generating the result table w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
ConvertToTable = Table.FromRows(List.Split(List.Accumulate(
Text.ToList(Text.Remove(Text.Combine(Source[String]), " ")), {}, (s, c) => if Value.FromText(List.Last(s)) is number and Value.FromText(c) is number then List.RemoveLastN(s, 1) & {List.Last(s) & c} else s & {c}
), 2), {"Letters", "Quantity"}),
Solution = Table.Sort([Table.Group](http://table.group/)(ConvertToTable, {"Letters"}, {{"Quantity", each List.Sum(List.Transform([Quantity], Number.From))}}), "Letters")
in
Solution

LinkedIn Post by:
Krzysztof Kominiak

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 AddPos = Table.AddColumn(Source, "Pos", each List.Reverse(List.Skip(List.PositionOfAny(Text.ToList([String]), {"A".."Z"} ,2)))),
 SelCol = Table.AddColumn(AddPos, "Letters", each List.Accumulate( [Pos], [String], (s,c)=> Text.Insert(s, c, "||")))[[Letters]],
 SplitCol = Table.ExpandListColumn(Table.TransformColumns(SelCol, {{"Letters", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Letters"),
 RemSpaces = Table.ReplaceValue(SplitCol," ","",Replacer.ReplaceText,{"Letters"}),
 AddNumb = Table.AddColumn(RemSpaces, "Numb", each Number.From(Text.Select([Letters], {"0".."9"} ))),
 SelLetters = Table.TransformColumns(AddNumb, {"Letters", each Text.Remove(_, {"0".."9"} )} ),
 Result = Table.Sort(Table.Group(SelLetters, {"Letters"}, {{"Quantity", each List.Sum([Numb]),type number}}), {"Letters"} )
in
 Result

LinkedIn post by:
JvdV

=LET(x,DROP(REDUCE(0,A2:A5,LAMBDA(a,b,VSTACK(a,LET(c,TEXTSPLIT(b,,CHAR(ROW(65:90)),1),HSTACK(TRIM(TEXTSPLIT(b,,c,1)),--c))))),1),y,TAKE(x,,1),REDUCE({"Letters","Quantity"},SORT(UNIQUE(y)),LAMBDA(e,f,VSTACK(e,HSTACK(f,SUM(IF(y=f,DROP(x,,1))))))))

LinkedIn Post by:
Caroline Blake

=LET(_a,CONCAT(A2:A5),_b,TEXTSPLIT(_a," ",{1,2,3,4,5,6,7,8,9},TRUE),_c,TEXTSPLIT(_a,,_b,TRUE),_d,UNIQUE(_b),_e,MAP(_d,LAMBDA(_d,SUM(_c*(_d=_b)))),VSTACK(HSTACK("Letters","Quantity"),SORT(HSTACK(_d,_e),,1)))

LinkedIn Post by:
Sunny Baggu

2#
=LET(
 _str, TEXTJOIN(" ", , A2:A5),
 _alpha, CHAR(SEQUENCE(26, , CODE("A"))),
 _modstr, REDUCE(_str, _alpha, LAMBDA(a, v, SUBSTITUTE(a, v, v & " "))),
 _tbl, SORT(WRAPROWS(TEXTSPLIT(_modstr, , " ", 1), 2)),
 _qty, MAP(
 UNIQUE(DROP(_tbl, , -1)),
 LAMBDA(a, SUM(--FILTER(DROP(_tbl, , 1), DROP(_tbl, , -1) = a)))
 ),
 HSTACK(UNIQUE(DROP(_tbl, , -1)), _qty)
)

LinkedIn post by:
Daniel Madhadha

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Split Column by Character Transition" = Table.SplitColumn(Source, "String", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"String.1", "String.2", "String.3", "String.4", "String.5"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Character Transition", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Value.1", "Value.2"}),
#"Trimmed Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Split Column by Character Transition1", {{"Value.2", type text}}, "en-US"),{{"Value.1", Text.Trim, type text}, {"Value.2", Text.Trim, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Value.2", type number}}),
#"Grouped Rows" = [Table.Group](http://table.group/)(#"Changed Type", {"Value.1"}, {{"Count", each List.Sum([Value.2]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Value.1", Order.Ascending}})
in
#"Sorted Rows"

LinkedIn Post by:
Taeyong Shin

=LET(
 txt, REDUCE(A2:A5, SEQUENCE(10) - 1, LAMBDA(a,c, SUBSTITUTE(a, c & " ", c & ", ") )),
 s, SORT(TEXTSPLIT(ARRAYTOTEXT(txt), , ", ")),
 u, UNIQUE(LEFT(s)),
 SCAN( 0, HSTACK(u, u), LAMBDA(a,c,
 IF(ISNUMBER(a), c, SUM(IFERROR(NUMBERVALUE(s, " ", c), )) )
 ))
)