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
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.
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.
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
@@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.
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 😊
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 🤗
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 😊
