Excel BI Power Query Challenge Workout 077

EnterpriseDNA · May 30, 2023, 10:21am

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

EnterpriseDNA · May 30, 2023, 10:38am

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

EnterpriseDNA · May 30, 2023, 10:39am

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

EnterpriseDNA · May 30, 2023, 10:40am

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

EnterpriseDNA · May 30, 2023, 10:41am

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

EnterpriseDNA · May 30, 2023, 10:41am

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"

EnterpriseDNA · May 30, 2023, 10:42am

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

EnterpriseDNA · May 30, 2023, 10:43am

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

EnterpriseDNA · May 30, 2023, 10:43am

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

EnterpriseDNA · May 30, 2023, 10:44am

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"

EnterpriseDNA · May 30, 2023, 10:45am

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

EnterpriseDNA · May 30, 2023, 10:45am

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

EnterpriseDNA · May 30, 2023, 10:46am

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"

EnterpriseDNA · May 30, 2023, 10:47am

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

EnterpriseDNA · May 30, 2023, 10:47am

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

EnterpriseDNA · May 30, 2023, 10:48am

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

EnterpriseDNA · May 30, 2023, 10:49am

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

EnterpriseDNA · May 30, 2023, 10:49am

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

EnterpriseDNA · May 30, 2023, 10:50am

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"

EnterpriseDNA · May 30, 2023, 10:50am

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

Excel BI Power Query Challenge Workout 077

LinkedIn Post by: Alejandra Horvath CPA, CGA

LinkedIn Post by: Krzysztof Nowak

LinkedIn Post by: Alejandro Simón

LinkedIn Post by: Bo Rydobon

LinkedIn Post by: Brian Julius

LinkedIn Post by: Mahmoud Bani Asadi

LinkedIn Post by: Eric Laforce

LinkedIn Post by: Taeyong Shin

LinkedIn Post by: Omid Motamedisedeh

LinkedIn Post by: Luan Rodrigues

LinkedIn Post by: Victor Wang

LinkedIn Post by: Hussein SATOUR

LinkedIn Post by: Zoran Milokanović

LinkedIn Post by: Krzysztof Kominiak

LinkedIn post by: JvdV

LinkedIn Post by: Caroline Blake

LinkedIn Post by: Sunny Baggu

LinkedIn post by: Daniel Madhadha

LinkedIn Post by: Taeyong Shin

LinkedIn Post by:
Alejandra Horvath CPA, CGA

LinkedIn Post by:
Krzysztof Nowak

LinkedIn Post by:
Alejandro Simón

LinkedIn Post by:
Bo Rydobon

LinkedIn Post by:
Brian Julius

LinkedIn Post by:
Mahmoud Bani Asadi

LinkedIn Post by:
Eric Laforce

LinkedIn Post by:
Taeyong Shin

LinkedIn Post by:
Omid Motamedisedeh

LinkedIn Post by:
Luan Rodrigues

LinkedIn Post by:
Victor Wang

LinkedIn Post by:
Hussein SATOUR

LinkedIn Post by:
Zoran Milokanović

LinkedIn Post by:
Krzysztof Kominiak

LinkedIn post by:
JvdV

LinkedIn Post by:
Caroline Blake

LinkedIn Post by:
Sunny Baggu

LinkedIn post by:
Daniel Madhadha

LinkedIn Post by:
Taeyong Shin