Table.Split and Table.SplitAt - Power Query

[@asimonr]

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

[@alejandrahorvath]

Muchas gracias Alejandro! Saludos 😊

[@franciscom.paredesarias2356]

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

[@alejandrahorvath]

Muchas gracias Francisco! Saludos 🤗

[@_GB84]

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

[@alejandrahorvath]

Glad you found it useful Gary! Thanks for watching 😊

[@OdelLeal]

Alejandra, Excellent results.

[@alejandrahorvath]

Thank you Odel! Saludos 😊

[@kestaanalytics]

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

[@alejandrahorvath]

Glad it helped! 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 😊

[@antonysovy5887]

as you always say, excellent!!

[@alejandrahorvath]

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

[@tobesoria]

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

[@alejandrahorvath]

😊

[@zahoorsarbandi2982]

Very interesting and full of knowledge. Thanks

[@alejandrahorvath]

Glad you enjoyed it! Thanks for watching 😊

[@eduardomunoz2764]

Magistral como siempre, saludos estimada Maestra Ale.

[@alejandrahorvath]

Muchas gracias Eduardo! Un abrazo 😊

[@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 😊

[@Ratnakumarwrites]

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

[@alejandrahorvath]

Glad to hear that. Thanks for watching 😊

[@SamehRSameh]

Excellent 👌👍👍

[@alejandrahorvath]

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 😊

[@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.

[@boissierepascal5755]

Awsome !

[@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 😊

[@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 😊