Actual I want this 1 1 null 1 null 1 null 1 null 1 1 1 null null null null null null null null null null null null null null is it possible i want fill down from 1 to 1
thanks Ruth, I like these PQ puzzles. Never saw that : first Unpivot and then pivot : Amazing !! I think that a good data analist really needs a good toolbox , full of tricks to fight the messy data.
Hi Ruth, I just had a quick question. I was wondering if you are able to fill-down/fill-up for columns if the data is connected via Direct Query connection (live data)? Do you know the answer to this question? I've been searching around and have not found an answer.
In the first example, what if the value in Column 1 is static? for example, there isn't "Value 1, 2, 3, but just a simple "yes" or "no", and you want to create a series/index?
Ruth it is the perfect example of how to extract ghee/butter by bending the finger slightly cross when it is very difficult to extract in straight way. Very clever trick. Thanks for sharing.
What is the best way to update the records in the source tables/sheets form the Power Query ( like, adding records, deleting a record, editing,.....). Can we make a column properties ( one or more columns) a Hyperlink that be clicking it, a custom data entry form be opened to add, deleted or update the record/s in the source tables?
What dirty data are you typically ingesting? From time to time it's more reasonable to train people to store clean(er) data instead of fixing everything in PowerQuery just because it's possbile. If it's a one time load, it's an option, but you can never foresee what chaos users will come up with next if this is supposed for repeated refresh, so you better agree on a format upfront instead of fixing current data.
@@CurbalEN Scraping data from web pages - a hell as well. Btw, do you want to make a video on that topic? Espacially when looking for a stable solution that deals with refreshes and equally structured sub-pages? What I don't like about the Power Query solution is that it counts elements in the HTML tree to navigate to the data which is not at all adaptive to changes or variations, even if pages look the same. I'd prefer XPath definitions to address elements to load into Power BI. Have you heard of a good solution for that?
One other version/solution of these types of scenarios. You can create a conditional column, then temporarily hide rows do you can fill up or down, then unfilter.
How can I fill down in a cyclic manner? E.g., say I have "Value1", "Value2" and "Value3" and want to fill down such that I have permuting "Value1", "Value2", "Value3", "Value1", "Value2" , "Value3", ... until the end of the column?
Thank you so much!!! Exports from Quickbooks insert a category on line 1 and then items from row 2 onwards with all cells in line 1 blank after the first row. This has helped enormously in being able to fill down which eluded me for an hour.
Hi Ruth, for me the answer is #4 and made it with PQ. All from calendar table, coincidences from fiscal. Manage two tables with data is not recommended... I think!
Thanks 😊, it took time for me too to figure it out, but hopefully after watching the video, it will be just a few seconds to make it work for everyone else! /Ruth
Answer 4 makes the most sense. 2 and 3 won't work because year is not a unique row in any of these tables, and using 1 would destroy a one to many relationship based on the date column to a fact table.
Thanks. I did something similar in your first example. I had not actually thought of using pivot /unpivot to accomplish the result. Even though I recognized that was similar to the structure
I have two tables with different format and want to create two master tables one will be employee and other will be vendor. but after creating those tables their will be no relation between masters so want to create relationship between those masters. so how to solve this with DAX.
Hello, thanks for your videos!! Do you have any ideia if is possible to fill down or up by only a single row instead of filling up till the next row that has value? I have a dataset that the row sequence are not standard..therefore i need to find a way to fill down or up when a condition is met....is like a hiararchy = ITEM A has its invoice B...invoice B is a child...but there are some cases that some itens will have no invoices.
Correct answer is Number 3. Number 1 and number 4 are basically the same and would result in duplicate date values in the date table which is not correct. Number 2 will result in a Many:Many relationship (because both tables have each year more than one time of course) and hence it is disastrous plus it will not achieve the desired outcome
Answer to Q29 = #2 ... You will end up with a snow flake, but this would easily get the job done... I'm back to commuting this month after 2mo of working from home... meaning I'm also back to watching your videos 1 day late... 😒 Thanks, Ruth!
40 km/40 min drive each way. I know it could be worse, but that's 80 min/day I could be learning or more productive... (also Greta Thunberg would be a lot happier)
I think the answer is No.4: Merge Fiscal into the Date Table. If both Fiscal and Date tables have the same schema, better to merge them in power query (using the Full Outer: Rows from both tables) which is the safest join so that you won't lose any data from both tables.
Yes. It is a trick one. The correct answer will be have a Calendar Table that already include FIscal time (Year, Weeks, etc) In this case, would be ideal to have just one Calender table, so merge will be the best solution for it. #4
Q29 answer: 4. I normally use 1 date table. If there are 2 tables in this model, I would merge them into one in PQ. Then we can drop fiscal month and calendar month to an axis of a line chart for example.
Thanks so much for this . The fill down trick helped me a lot when I was cleaning a pdf file. Your topics are very practical and Easy to follow . I always watch your vids. Keep it up.
Hello :) I just want to ask how the columns I added through dax would appear to my table when editing in power query? I tried to refresh the data but it won't show up. Thank you! 🙂
@@CurbalEN oh because I tried to do the networkdays showed in one of your videos and I need the data in power query. Is there another way to do the networkdays in power query?
Actual I want this
1 1
null 1
null 1
null 1
null 1
1 1
null null
null null
null null
null null
null null
null null
null null
is it possible i want fill down from 1 to 1
Grreat...Thanks a ton for providing this simple but unknown and very helpful solution.
There totally needs to be more "Tada's" in life! Made me smile ear to ear! Thank you!
Thanks Kimberly!! :)
Gracias! Exactamente lo que estaba buscando!!! Gracias gracias!
thanks Ruth, I like these PQ puzzles. Never saw that : first Unpivot and then pivot : Amazing !! I think that a good data analist really needs a good toolbox , full of tricks to fight the messy data.
Glad you enjoyed it!
Hi Ruth, I just had a quick question.
I was wondering if you are able to fill-down/fill-up for columns if the data is connected via Direct Query connection (live data)?
Do you know the answer to this question? I've been searching around and have not found an answer.
Thank You for creating this video with great examples. I was able to learn something new
In the first example, what if the value in Column 1 is static? for example, there isn't "Value 1, 2, 3, but just a simple "yes" or "no", and you want to create a series/index?
Wunderbar. Need the first example for sure, so thanks for that. BTW, I think it's number 4 :-)
Great!!! :)
Ruth it is the perfect example of how to extract ghee/butter by bending the finger slightly cross when it is very difficult to extract in straight way. Very clever trick. Thanks for sharing.
Thanks 😊!
What is the best way to update the records in the source tables/sheets form the Power Query ( like, adding records, deleting a record, editing,.....). Can we make a column properties ( one or more columns) a Hyperlink that be clicking it, a custom data entry form be opened to add, deleted or update the record/s in the source tables?
What dirty data are you typically ingesting? From time to time it's more reasonable to train people to store clean(er) data instead of fixing everything in PowerQuery just because it's possbile. If it's a one time load, it's an option, but you can never foresee what chaos users will come up with next if this is supposed for repeated refresh, so you better agree on a format upfront instead of fixing current data.
Cleaning pdfs.....a nightmare... :(
/Ruth
@@CurbalEN Scraping data from web pages - a hell as well. Btw, do you want to make a video on that topic? Espacially when looking for a stable solution that deals with refreshes and equally structured sub-pages? What I don't like about the Power Query solution is that it counts elements in the HTML tree to navigate to the data which is not at all adaptive to changes or variations, even if pages look the same. I'd prefer XPath definitions to address elements to load into Power BI. Have you heard of a good solution for that?
Just what I was looking for! Thank you so much!
Thanks you just gave me some Idea on what am working on
One other version/solution of these types of scenarios. You can create a conditional column, then temporarily hide rows do you can fill up or down, then unfilter.
How can I fill down in a cyclic manner? E.g., say I have "Value1", "Value2" and "Value3" and want to fill down such that I have permuting "Value1", "Value2", "Value3", "Value1", "Value2" , "Value3", ... until the end of the column?
Did it with Table.Repeat and Index column
Thanks a lot, I spend a lot of hours on this logic
This is really cool! Thank you so much😇
I love when she says wait for the magic......TA DAAANNNN!!!
😂😂 I am such a nerd sometimes!
Awesome video... solved my problem at hand! Thank you
Wonderful to hear!
Thank you so much!!! Exports from Quickbooks insert a category on line 1 and then items from row 2 onwards with all cells in line 1 blank after the first row. This has helped enormously in being able to fill down which eluded me for an hour.
Take a espresso to celebrate ;)
Tadaaaaa :) . . Amazing... Really good work Ruth
👏👏👏
Hi Ruth, for me the answer is #4 and made it with PQ. All from calendar table, coincidences from fiscal. Manage two tables with data is not recommended... I think!
Que buena!!!
So simple but brilliant. I would have spent hours trying to work it out lol. Thank you Ruth 😉
Thanks 😊, it took time for me too to figure it out, but hopefully after watching the video, it will be just a few seconds to make it work for everyone else!
/Ruth
fantastic hack!
Love u 💕 nice one
Option 4.
Answer 4 makes the most sense. 2 and 3 won't work because year is not a unique row in any of these tables, and using 1 would destroy a one to many relationship based on the date column to a fact table.
Noted!
/Ruth
Hi Ruth.. truly genious ! I was struggling for this on a data set.Thank you so so so much..
Thanks. I did something similar in your first example. I had not actually thought of using pivot /unpivot to accomplish the result. Even though I recognized that was similar to the structure
This is amazing - saved me so much time! suscribed
Welcome!!
I have two tables with different format and want to create two master tables one will be employee and other will be vendor. but after creating those tables their will be no relation between masters so want to create relationship between those masters. so how to solve this with DAX.
Hello, thanks for your videos!! Do you have any ideia if is possible to fill down or up by only a single row instead of filling up till the next row that has value? I have a dataset that the row sequence are not standard..therefore i need to find a way to fill down or up when a condition is met....is like a hiararchy = ITEM A has its invoice B...invoice B is a child...but there are some cases that some itens will have no invoices.
Maybe this will give you ideas:
m.ruclips.net/video/TPeJdobqS9k/видео.html
Thankkk youuu 😀
Correct answer is Number 3.
Number 1 and number 4 are basically the same and would result in duplicate date values in the date table which is not correct.
Number 2 will result in a Many:Many relationship (because both tables have each year more than one time of course) and hence it is disastrous plus it will not achieve the desired outcome
Noted!
/Ruth
Incredible!! More of this please :)
Answer to Q29 = #2 ... You will end up with a snow flake, but this would easily get the job done...
I'm back to commuting this month after 2mo of working from home... meaning I'm also back to watching your videos 1 day late... 😒
Thanks, Ruth!
It is never too late!!
Do you have a long commute?
/Ruth
40 km/40 min drive each way. I know it could be worse, but that's 80 min/day I could be learning or more productive... (also Greta Thunberg would be a lot happier)
@@HachiAdachi agreed, we dont want upset Greta!
;)
Excellent Power Query work Ruth, Love it!
Thanks Ruth for these two wonderful query tips
Thanks Amr!
/Ruth
4
Noted :)
Hi Ruth, I believe merging the Date and the Fiscal table based on date key should work, so I'll go with option 4.
Brilliant Ruth 👍👍🍜🍜
🎉🎉
Legend
😊
Power BI master
🤣🤣 thanks!
very Nice tricks
Thanks! 😊
OMG! That was soooooooooo helpful!
Ruth you killed me with ex.2! Very good.
Thanks!! 😊
/Ruth
Good Trick, Could you please make some videos on Time Elapsed calculations in power query thanks.
Noted :)
I think the answer is No.4: Merge Fiscal into the Date Table. If both Fiscal and Date tables have the same schema, better to merge them in power query (using the Full Outer: Rows from both tables) which is the safest join so that you won't lose any data from both tables.
Noted :)
/Ruth
Genius! :)
🥳
Amazing query tips, Ruth. I choose option 4: Merge Fiscal into the Date Table. Always keep one calendar table. :P
Got it :)
/Ruth
Yes. It is a trick one. The correct answer will be have a Calendar Table that already include FIscal time (Year, Weeks, etc)
In this case, would be ideal to have just one Calender table, so merge will be the best solution for it. #4
Got it Carlos!
/Ruth
Option 2 :- add fiscal to the model and create one to many relationship by using date[year] and fiscal [year]
Got it :)
Q29 answer: 4. I normally use 1 date table. If there are 2 tables in this model, I would merge them into one in PQ. Then we can drop fiscal month and calendar month to an axis of a line chart for example.
Thanks Vida!
/Ruth
Thanks so much for this . The fill down trick helped me a lot when I was cleaning a pdf file. Your topics are very practical and Easy to follow . I always watch your vids. Keep it up.
That is what I am cleaning loads at the moment ;)
i will go with Option 4, if i add fiscal info to Existing date table already relation is there while displaying we can use calendar column as well.
Noted :)
/Ruth
I believe the answer to question 29 is option 4 because it's redundant to have a model that stores an additional table for fiscal dates.
Got it:)
Estos problemas son muy recurrentes cuando bajo reportes de un sistema. Gracias Ruth por estos trucos.
Perfecto José Manuel :)
/Ruth
Great tutorial! Really helpful, thank you!
Wonderful to hear!
Awesome, very creative methods!
Thank you! Cheers!
Hello :) I just want to ask how the columns I added through dax would appear to my table when editing in power query? I tried to refresh the data but it won't show up. Thank you! 🙂
They dont, only the other way around.
/Ruth
@@CurbalEN oh because I tried to do the networkdays showed in one of your videos and I need the data in power query. Is there another way to do the networkdays in power query?
Probably, ask in the power bi community and give as many details as possible!
/Ruth
🤯🤯🤯 thank you so much!
Simple but powerful tricks!
/Ruth
Option 2
Got it :)
Option 2
Noted :)
Option 2
Got it!
/Ruth
Ans. 3
Noted!
Thanks
You Welcome!
Showwww
Aprendo muito.com vc e nem sei falar ingles..imagina!
Super!