Це відео не доступне.
Перепрошуємо.
Power Query - Split Table Dynamically
Вставка
- Опубліковано 5 сер 2024
- 🚀 Explore the power of Power Query with our latest tutorial! Learn how to dynamically split your dataset into two using the powerful Table.SplitAt function. 📊 Watch as we guide you through the process of reconstructing and pivoting your tables seamlessly using nested Let statements. 💡 Level up your data transformation game with this step-by-step tutorial! 🔧
#powerquery #dataanalytics #powerbi #powerbidesktop #mquery #DataTransformation #ExcelTips #TutorialTime 🎥
Follow along files
github.com/jbotes/powerbiTuto...
github.com/jbotes/powerbiTuto...
M-query functions used
Table.split to Split a table into many
learn.microsoft.com/en-us/pow...
List.transform to create a new list applying a transform to a list.
learn.microsoft.com/en-us/pow...
table.selectrows returns table that meets specific conditions
learn.microsoft.com/en-us/pow...
Table.removecolumns to remove columns from a table
learn.microsoft.com/en-us/pow...
Table.promoteheaders to promote the rows in a table to the header
learn.microsoft.com/en-us/pow...
Table.unpivotothercolumns to translate all other columns (other than specified) into attribuet value pairs.
learn.microsoft.com/en-us/pow...
Table.combine returns a table by merging a list of tables.
learn.microsoft.com/en-us/pow...
Highlighted Techniques
1 - Using a Nested Let Statement in a new column
2 - Using a variable in a query (as step) to feed a value in a function
Thank you for making these videos!
I like how you maneged to learn the M language that well, that you can write the let statements within the steps to combine some logicals steps without throwing errors all the time. Due to your videos, I’m trying to use the ribbon less and less myself. It really works for me to get better at M and improve the endless possiblilities. Thank you for keep making these videos!
That's so awesome! thank you ! I find the more one uses m-query instead of the UI the more cool things you can start doing. Basically anything is possible if you know the language!
@@basensei8699 whatt???/
Excellent.
Great stuff 👌
Nice video, clustered lets are so useful in Power Query
Thanks man! yes i think i'm gonna make a video on Clustered lets by itself.
@@basensei8699 Great idea!
Marvelous 🎉🎉
i want to use merge query if condition achieved how ?
I have a data set that comes in as a notepad single table. I want to dynamically split it into 10+ tables based on their single column titles. Can i repeat the steps in this video to dynamically split 1 table into many?
Hi Bro, if i have a list {18,46,95} , and need to split table at these rows , how we do this? could you please assist?
I have a large data set that is actually 10 tables that appear to excel as 1 table. Can i dynamically split that table into the 10 tables i need it to be by repeating the steps here 10 times or is there a better way?
hi James. Excellent video as always. I am wondering how you would deal if you have a raw table with more than 2 lines of break (blank) rows. Table.SplitAt function can only split in one location at a time. If you have a solution for such cases, please advise. Thank you🌹👍.
thats a great question. the table.splitat function can only split a table into 2. But to overcome this i would do a nested splits over the entire data set. Do the first split. then the second split with have a table that needs to be split more. Then i split that table. and the next one looping through until all ahs been split. This would be a great idea for a future video. I'll make note of this one.
@basensei8699hi, this is a great video. Just wanted to see if you made a video on dealing with more than 2 tables..??
I am struggling with SplitAt formula. Everytime I enter the formula, I get an error message "Expression.Error: We cannot convert a value of type Table to type Number.
Details:
Value=[Table]
Type=[Type]"
What do I do now? Please help!