While the right click to import data as a Table works beautifully. I find having a Quick Access icon with Table import is my best option for bringing in Tables. Thank you for your outstanding work on Power Query, etc.
I agree 100% I love customizing my QAT. Have you seen my video on the "Complete Guide to the QAT"? Check it out; I bet there are some tricks you may not have known. Cheers. ua-cam.com/video/GEv7_AHtv-w/v-deo.html
Yes; many viewers are contributing some AMAZING alternate solutions. Check out some of the other replies for ideas similar to your own. Thanks for watching.
That is an EXCELLENT way of solving the problem using Dynamic Array functions. I love DA functions!!! When I use DA function for this, I am not getting the rank for the first entry ("McDonald's") to display. What about you? This is odd. Trying to figure out why. Thanks for your great solution!!!
When I recreated the data in a new file, I didn't have the problem of the missing first rank. But no matter where I put that formula in the download file, the first rank is blank. "1" show up in the formula audit trail, but not in the displayed output. This is very odd, indeed.
@@bcti-bcti yes, the dynamic array functions have really changed the way I tackle problems in Excel. Each tool has its strengths, so it'd be cool if PQ could evaluate excel functions, like how Excel could call python to return a dataset. For example, EXCEL(table_reference, "=TAKE(WRAPROWS (table_reference, 5),,4)"). Would probably be a nightmare on query folding though.
I like your thinking! I've made an adjustment to the formula to discard the column of zeroes (blank rows in source). =DROP(INDEX(A1:A254,SEQUENCE(ROWS(A1:A254)/5,5)),,-1) If you're using Dynamic Array functions (and I assume you are since you are using the SEQUENCE function), a shorter version offered by another viewer is: =TAKE(WRAPROWS(Sales, 5),,4) Thanks for watching and contributing. It's always great to see alternate solutions. Cheers!
Though wrap rows is simple way to do this, i want to do this with my favorite Reduce Function =LET(TheMagicNumber,5,Data,A1:A254, REDUCE(TOROW(TAKE(Data,TheMagicNumber),1), SEQUENCE(ROWS(Data)/TheMagicNumber,1,TheMagicNumber,TheMagicNumber), LAMBDA(acc,curr,VSTACK(acc,TOROW(OFFSET(A1,curr,0,TheMagicNumber,1),1)))))
Agreed! But for those who do not have access to Dynamic Array functions, Power Query is an option. Plus, this video also served as a way to demonstrate a variety of Power Query features for use in other situations. Sidenote: you would still need to filter out the resulting 5th column of zeroes, as well as the other 209K+ rows at the bottom of the output. Thanks for your ideas and time to watch. Cheers.
Hi ! Thank you for your work. A shorter way with M code. let Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content][Column1], TransfTable = Table.FromRows(List.Split(List.RemoveNulls(Source),4),{"Rank","Company","Category","Sales"}), Currency= Table.TransformColumnTypes(TransfTable,{{"Sales", Currency.Type}}) in Currency
I LOVE IT!!!! That is fantastic! I have split your single step into separate steps so it's easier to understand for beginners. Thank you for a great solution. Different tactics are ALWAYS appreciated. let Source = Excel.CurrentWorkbook(){[Name = "Sales"]}[Content][Column1], #"Remove Nulls" = List.RemoveNulls(Source), #"Records to Lists" = List.Split(#"Remove Nulls", 4), #"Lists to Records" = Table.FromRows( #"Records to Lists", {"Rank", "Company", "Category", "Sales"} ), #"Set Data Types" = Table.TransformColumnTypes( #"Lists to Records", {{"Rank", Int64.Type}, {"Company", type text}, {"Category", type text}, {"Sales", Int64.Type}} ) in #"Set Data Types"
Absolutely pro-solutions, as always. Thank you for sharing them!!!
@@IvanCortinas_ES My pleasure. Thanks for watching!
Thank you, Professor. Great explanation.
@@kkravch Thank you! Now go out and conquer the world.
Excellent techniques, clearly explained. Many thanks.
@@iankr Thank you. Could you tell I have a head cold?
Excellent solution and explanation.
Thank you. Many viewers are contributing some AMAZING alternate solutions. Check out some of the great ideas in the comments.
While the right click to import data as a Table works beautifully. I find having a Quick Access icon with Table import is my best option for bringing in Tables. Thank you for your outstanding work on Power Query, etc.
I agree 100% I love customizing my QAT. Have you seen my video on the "Complete Guide to the QAT"? Check it out; I bet there are some tricks you may not have known. Cheers.
ua-cam.com/video/GEv7_AHtv-w/v-deo.html
helpful, wraprows also could be used as well here
Yes; many viewers are contributing some AMAZING alternate solutions. Check out some of the other replies for ideas similar to your own. Thanks for watching.
Excellent, thank you!
Thank you very much. We greatly appreciate your support and engagement.
Solution 2 is quite impressive. I appreciate you sharing it.
@@Bhavik_Khatri My pleasure. Thanks for watching.
Thank you for the great content as always
My pleasure! Thanks for watching.
Really waiting your new tutorials, always found amazing and excellent and yes applicable to working world
Hats off
@@arnyanderson4955 Thank you! It’s nice to feel appreciated. Cheers!
Thanks for the handy little tips, especially the non conversion of data to a table by naming the range
I love that trick! I wish that was a behavior we could turn off in the PQ settings. Thanks for watching.
=wraprows(filter(sales, sales""), 4)
Safer, in case of empty cells in data:
=TAKE(WRAPROWS(SALES, 5),,4)
That is an EXCELLENT way of solving the problem using Dynamic Array functions. I love DA functions!!! When I use DA function for this, I am not getting the rank for the first entry ("McDonald's") to display. What about you? This is odd. Trying to figure out why. Thanks for your great solution!!!
When I recreated the data in a new file, I didn't have the problem of the missing first rank. But no matter where I put that formula in the download file, the first rank is blank. "1" show up in the formula audit trail, but not in the displayed output. This is very odd, indeed.
It works in the download file for me. This is with Excel on Android.
:)
@@bcti-bcti yes, the dynamic array functions have really changed the way I tackle problems in Excel. Each tool has its strengths, so it'd be cool if PQ could evaluate excel functions, like how Excel could call python to return a dataset.
For example, EXCEL(table_reference, "=TAKE(WRAPROWS (table_reference, 5),,4)").
Would probably be a nightmare on query folding though.
Excellent.
Do you use Camtasia for your videos?
@@ismaelkourouma5558 Yes. Is it that obvious? 🤣
Always good to know PQ stuff, but easier for this example:
=INDEX(A1:A254,SEQUENCE(ROWS(A1:A254)/5,5))
I like your thinking! I've made an adjustment to the formula to discard the column of zeroes (blank rows in source).
=DROP(INDEX(A1:A254,SEQUENCE(ROWS(A1:A254)/5,5)),,-1)
If you're using Dynamic Array functions (and I assume you are since you are using the SEQUENCE function), a shorter version offered by another viewer is:
=TAKE(WRAPROWS(Sales, 5),,4)
Thanks for watching and contributing. It's always great to see alternate solutions. Cheers!
Though wrap rows is simple way to do this, i want to do this with my favorite Reduce Function
=LET(TheMagicNumber,5,Data,A1:A254,
REDUCE(TOROW(TAKE(Data,TheMagicNumber),1),
SEQUENCE(ROWS(Data)/TheMagicNumber,1,TheMagicNumber,TheMagicNumber),
LAMBDA(acc,curr,VSTACK(acc,TOROW(OFFSET(A1,curr,0,TheMagicNumber,1),1)))))
I always enjoy seeing how other people solve the same problem. Great job!
Overly complicated. No need for Power Query. Use formula =WRAPROWS(A:A,5) and viola!
Agreed! But for those who do not have access to Dynamic Array functions, Power Query is an option. Plus, this video also served as a way to demonstrate a variety of Power Query features for use in other situations.
Sidenote: you would still need to filter out the resulting 5th column of zeroes, as well as the other 209K+ rows at the bottom of the output.
Thanks for your ideas and time to watch. Cheers.
Hi ! Thank you for your work. A shorter way with M code.
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content][Column1],
TransfTable = Table.FromRows(List.Split(List.RemoveNulls(Source),4),{"Rank","Company","Category","Sales"}),
Currency= Table.TransformColumnTypes(TransfTable,{{"Sales", Currency.Type}})
in
Currency
I LOVE IT!!!! That is fantastic! I have split your single step into separate steps so it's easier to understand for beginners. Thank you for a great solution. Different tactics are ALWAYS appreciated.
let
Source = Excel.CurrentWorkbook(){[Name = "Sales"]}[Content][Column1],
#"Remove Nulls" = List.RemoveNulls(Source),
#"Records to Lists" = List.Split(#"Remove Nulls", 4),
#"Lists to Records" = Table.FromRows(
#"Records to Lists",
{"Rank", "Company", "Category", "Sales"}
),
#"Set Data Types" = Table.TransformColumnTypes(
#"Lists to Records",
{{"Rank", Int64.Type}, {"Company", type text}, {"Category", type text}, {"Sales", Int64.Type}}
)
in
#"Set Data Types"
@bcti-bcti 👏🏼👏🏼👏🏼