Table.Split and Table.SplitAt - Power Query

Hello everyone,
Today I have an example to show you the Table.Split and Table.SplitAt functions.
Hope you'll find it useful.
Remember to subscribe and click at the bell, so you'll receive notifications when new videos are posted. 🔔
Have a wonderful day and thanks for watching 😃
Working file available here:
drive.google.com/drive/folder...
#MSExcel #PowerQuery #TableSplit #TableSplitAt #TimeSavingSolutions #PowerQueryGirl #AnotherDayWithPowerQuery #AlejandraHorvath #Empowered2XL

[@kestaanalytics]

Thank you. I got the answer I needed. the first 3 minutes of watching your video

[@alejandrahorvath]

Glad it helped! Thanks for watching 😊

[@asimonr]

Gracias Alejandra, mas claro imposible. Excelente entrega!!!!

[@alejandrahorvath]

Muchas gracias Alejandro! Saludos 😊

[@_GB84]

You're the best! Thank you for helping me solve this problem!

[@alejandrahorvath]

Glad you found it useful Gary! Thanks for watching 😊

[@roywilson9580]

It is great to see you back posting videos!
Thanks for the interesting and useful video. Nice use of table.splitat. I would make a small change to the steps to avoid hardcoding the split point of the tables by inserting few steps adding an index column, filtering down to a known value found in the row to split at and drilling down to the index number:
......
AddIndex = Table.AddIndexColumn( Table001, "Index", 1),
DrillDown = Table.SelectRows(AddIndex,each [Column13] = null)[Index]{0},
PrdcDataSet = List.Transform(Table.SplitAt(AddIndex, DrillDown), each
let
a = Table.RemoveColumns(_, "Index"),
b = Table.PromoteHeaders(a),
c = Table.SelectRows(b, each [#"Product #"] null),
d = Table.UnpivotOtherColumns( c, {"Product #"}, "Year - Qtr", "Amount"),
e = Table.SplitColumn(d, "Year - Qtr", Splitter.SplitTextByAnyDelimiter({" - "}),{"Year", "Qtr"})
in e
.........
Not overly important on such a small dataset but a for a large dataset it might save a lot of scrolling around.

[@alejandrahorvath]

It's great to be back 😊
Thank you very much for sharing!
Very appreciated 🙏
Thanks for watching 😊

[@boissierepascal5755]

Awsome !

[@tobesoria]

Excelente mini training💪🤗. gracias
Por otro lado, se te extraña mucho con los "live"

[@alejandrahorvath]

😊

[@SamehRSameh]

Excellent 👌👍👍

[@alejandrahorvath]

Thanks for watching 😊

[@OdelLeal]

Alejandra, Excellent results.

[@alejandrahorvath]

Thank you Odel! Saludos 😊

[@franciscom.paredesarias2356]

Muchas Gracias @Alejandra Horvath siempre algo nuevo que aprender con tus entregas. Saludos desde Chile

[@alejandrahorvath]

Muchas gracias Francisco! Saludos 🤗

[@tb9359]

Great job! I enjoy your videos and learn a huge amount of tips. Thank you so much!!

[@alejandrahorvath]

I'm so glad! Thanks for watching 😊

[@zahoorsarbandi2982]

Very interesting and full of knowledge. Thanks

[@alejandrahorvath]

Glad you enjoyed it! Thanks for watching 😊

[@asheeshpahwa7508]

Thanks 😊
Keep bringing these videos .
I would request you to please make videos on the excel bi challenges that comes everyday on linkedin
Thanks in advance

[@alejandrahorvath]

Thank you for watching and for your request 😊

[@antonysovy5887]

as you always say, excellent!!

[@alejandrahorvath]

Thank you Antony! I'm glad you liked it 😊

[@eduardomunoz2764]

Magistral como siempre, saludos estimada Maestra Ale.

[@alejandrahorvath]

Muchas gracias Eduardo! Un abrazo 😊

[@Ratnakumarwrites]

Great mam... I am learning from you... Love your videos

[@alejandrahorvath]

Glad to hear that. Thanks for watching 😊

[@osielg9889]

Hola, excelente explicación, una duda consideras que con muchos datos es más eficiente al resultado que tú realizaste únicamente tienes unos cuantos pasos en el editor, si lo hiciera utilizando las opciones normales desde la interfaz de usuario seguro quedarían muchos más. muchas gracias que estés muy bien 😁, te evi un fuerte abrazo 🤗

[@alejandrahorvath]

Hola, todo depende en la fuente de datos, las funciones que utilices y el orden en que apliques los pasos.
Menos número de pasos no significa más eficiencia, es lo que realizas y el orden en que lo realizas en cada paso (on en cada función).
Gracias por tu comentario. Saludos 😊

[@etienneleparisien1391]

Hello Alejandra, thanks very much for this very interesting video.
Do you have a video explaining how in PowerQuery to split a table in N tables according all the N distinct values ​​in a column ?
Thanks very much in advance for your help.

[@alejandrahorvath]

To split a table in N tables according all the N distinct values ​​in a column, you could select the column containing the values you want to split by, right click and select group by, select all rows.
The code would be something like this, if the table is in the current workbook:
let
Source = Excel.CurrentWorkbook(){[Name="TableName"]}[Content],
GroupedRows = Table.Group(Source, {"ColumnNameYouWantToSplitBy"}, {{"NewColumnName", each _}})
in
GroupedRows

[@etienneleparisien1391]

@@alejandrahorvath Thank you so much. I will try it at work. Jo estet és jo vasárnap (in Hungarian).

[@alejandrahorvath]

You're very welcome! Hope that this will solve what you were looking for. Have a great rest good evening and Sunday as well 😊

[@etienneleparisien1391]

@@alejandrahorvath Thanks to you. I could do it in VBA and SQL but I think the solution is smarter and faster to realize in PowerQuery.
After, I just need to change/replace dynamically the file source in the query formula by VBA.

[@DinoAMAntunes]

Hi Alejandra. Very good. when to choose between Table Ou Page, i have always this doubt? Tks in advance

[@alejandrahorvath]

Hi Dino,
This is a very good question.
In my experience the tables have worked better (for the most part); it all depends on the source.
You will need to evaluate the source and decide which is better.
Thanks for your question 😊

[@DinoAMAntunes]

@@alejandrahorvath Many Tks

[@alejandrahorvath]

You're most welcome 😊