Always love these challenges: the taking part; but most of all, seeing simple, clean solutions. I'm now reviewing my powerquery implementations to see if I can simplify my list-handling steps!
It's good to know that there is such a function Expression.evaluate(). I guess I was a bit lazy submitting {start..end} solution. :D Many thanks for the nice challenge and video Wyn!
Hi, indeed a great solution but in my humble opinion it is not the best (nor the fastest to code or execute). A simple demo with 340 rows already produces significant time differences. What I propose (I include my code -Apostolos): let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn( Source, "list_of_Nos", each List.Numbers( Number.FromText( Text.BeforeDelimiter([Ticket Range], "-") ), [Number of Ticket]) ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Number of Ticket", "Ticket Range"}), #"Expanded list_of_Nos" = Table.ExpandListColumn(#"Removed Columns", "list_of_Nos") in #"Expanded list_of_Nos" notes: use the List.Numbers with data from the table, no added columns, then remove unwanted columns 1st and then Expand... I Include the code for Eric's solution: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom1" = Table.AddColumn(Source, "Custom", each Expression.Evaluate( "{"& Replacer.ReplaceText( [Ticket Range],"-","..")&"}" )), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Name", "Custom"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom") in #"Expanded Custom" notes: again with removing 1st, then expanding Ok, I will close by just reminding that Evaluate existed/exists in VBA as well but is like a "pandora box" if not used with caution... Thanks anyway for a great presentation and all the tips :D
I'm a little sad, here: I'm subscribed to your channel and rang the bell and all, but I get to see these video's so darn late. I saw reference to this challenge on the Oz DuSoleil channel as a trigger... Anyway, here's my single cell solution: =LET(labels,F6:F8,data,G6:G8, splitdata,TEXTSPLIT(TEXTJOIN(";",,data),"-",";"), LB,TAKE(splitdata,,1), UB,TAKE(splitdata,,-1), RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","), RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,","), HSTACK(RangeLabel,RangeData)) Hope you like it. (Now I'll watch your video)
PS: here's my first update already. I like this better: it's more of a complete experience: 😉 =LET(FullTable,fRAW[#All],headers,TAKE(FullTable,1), labels,DROP(TAKE(FullTable,,1),1),data,DROP(TAKE(FullTable,,-1),1), LB,TEXTBEFORE(data,"-"), UB,TEXTAFTER(data,"-"), RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","), RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,",")+0, VSTACK(headers,HSTACK(RangeLabel,RangeData)))
Doesn't get better than this when the solution is easy to understand and you learn something new that will be useful going forward.
Absolutely 😀
Seen so far the best trick to convert values to a List.. Thank you so much. Appreciate
You're welcome. Thanks for letting me know
Always love these challenges: the taking part; but most of all, seeing simple, clean solutions. I'm now reviewing my powerquery implementations to see if I can simplify my list-handling steps!
It's great that everyone (including me) gets to learn a few new things each time
The 1st challenge I have ever watched here, which is just fab. Everyone showing off their secret recipe.😂
Good stuff… plenty others to watch 😀
Very cool solution by Eric! Thanks for running this and publishing a video Wyn!🎉
You're welcome
makes life so much easier....thanks y'all!
Glad it helps
It's good to know that there is such a function Expression.evaluate().
I guess I was a bit lazy submitting {start..end} solution. :D
Many thanks for the nice challenge and video Wyn!
So many different valid approaches!
Beautiful Solution There and great unique technique !
I love seeing how other people think
My suggestion is custom column as below:
[splt=List.Transform(Text.Split([Ticket Range],"-"),Number.From),
lst={splt{0}..splt{1}}
][lst]
Thanks for suggesting an approach
Thank you , lot of simple ways to do it.. :). I did the most toughest way ever possible to solve this problem :D using List.Generate.
Lots of ways to achieve the goal in Power Query 😀
Awesome! Thanks Wyn!!
Thanks Wayne
Cool solutions! Good! Thank you...
Cheers
Thank you so much ! perfect solutions...
You’re welcome.
All of these tricks were useful =)
Great
Blown away!
😀
👍💯
Hi, indeed a great solution but in my humble opinion it is not the best (nor the fastest to code or execute). A simple demo with 340 rows already produces significant time differences.
What I propose (I include my code -Apostolos):
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn( Source, "list_of_Nos", each List.Numbers( Number.FromText( Text.BeforeDelimiter([Ticket Range], "-") ), [Number of Ticket]) ),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Number of Ticket", "Ticket Range"}),
#"Expanded list_of_Nos" = Table.ExpandListColumn(#"Removed Columns", "list_of_Nos")
in
#"Expanded list_of_Nos"
notes: use the List.Numbers with data from the table, no added columns, then remove unwanted columns 1st and then Expand...
I Include the code for Eric's solution:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom1" = Table.AddColumn(Source, "Custom", each Expression.Evaluate( "{"& Replacer.ReplaceText( [Ticket Range],"-","..")&"}" )),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Name", "Custom"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom")
in
#"Expanded Custom"
notes: again with removing 1st, then expanding
Ok, I will close by just reminding that Evaluate existed/exists in VBA as well but is like a "pandora box" if not used with caution...
Thanks anyway for a great presentation and all the tips :D
Good approach
I'm a little sad, here: I'm subscribed to your channel and rang the bell and all, but I get to see these video's so darn late.
I saw reference to this challenge on the Oz DuSoleil channel as a trigger...
Anyway, here's my single cell solution:
=LET(labels,F6:F8,data,G6:G8,
splitdata,TEXTSPLIT(TEXTJOIN(";",,data),"-",";"),
LB,TAKE(splitdata,,1), UB,TAKE(splitdata,,-1),
RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","),
RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,","),
HSTACK(RangeLabel,RangeData))
Hope you like it.
(Now I'll watch your video)
PS: here's my first update already. I like this better: it's more of a complete experience: 😉
=LET(FullTable,fRAW[#All],headers,TAKE(FullTable,1),
labels,DROP(TAKE(FullTable,,1),1),data,DROP(TAKE(FullTable,,-1),1),
LB,TEXTBEFORE(data,"-"), UB,TEXTAFTER(data,"-"),
RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","),
RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,",")+0,
VSTACK(headers,HSTACK(RangeLabel,RangeData)))
PS: my input table does not have the "Number of tickets" column.
@@GeertDelmulle That's one Crazy Formula! I've not played with MAP in the real world yet. I'll have to check it out.
Oh wow, well that's my homework for tomorrow, expression.evalueate, I don't think I've ever seen it used before.
Nor me!