This is the best Video I have ever seen from you because even though you spoken in Polish, I understood you perfectly - your movement of the cursor, your use of the diagram on the second sheet and your highlighting the parts of the formula for emphasis was simply amazing!!! It was so good that I felt as if I DID understand Polish. But at the end, are you sure that Mike took all the classes!?!? Thank you very much, Bill "Power Query Artists and Poet" Szysz!!!
Thanks for the motivating comment !!! I will try to make future videos in this way :-)) Thank for watching, Mike. You are the legend of excel (to be sure... in Polish, to be a legend is a compliment) :-))
The video was really amazing because I followed you the whole way!!!! In Polish or English - you are a legend in Excel and Power Query!!!! I hope you became an Excel MVP sometime. I mentioned you to a few Microsoft people, and just said that I have not seen solutions like the ones you create from any one else. For me, you are THE smartest Power Query person. Plus, I get the added benefit of learning polish too : )
Dzięki za oglądanie :-) W PQ na początek zapomnijmy myśleć po "excelowemu"....to nie pomaga.... ale akurat w tym konkretnym przypadku, budowanie zapytania odbywa się prawie tak samo jak budowanie formuły w excelu. Jednak tak się dzieje tylko niekiedy.
Jestem pod wrażeniem! Bill, po prostu cudo! Jeszcze wieeeele power-querowej pracy przeze mną, żeby robić w tym narzędziu choćby ułamek tego, co Ty :). Dzięki za film!
Dzięki za obejrzenie filmu :-)) Skoro uważasz, że sporo drogi przed Tobą w PQ to znaczy, że wiele też radości z odkrywania fajnych sposobów i sposobików ;-)))
Thanks Bill! I was wondering what is your idea, and then seen this records concatenation trick - awesome! Masterpiece! As one of the fellows says, "you made complex transformation, and then Bill come into the scene with just one step"
Akurat teraz to mam we krwi cosik innego :-))) Dzięki za miłe słowa (acz chyba jednak przesadzone "trochę" ) :-))) Sam się przecież dobrze kręcisz w obszarze PQ :-))
Witam mam pytanie czy można przekształcić w power query np tabelę ale w ten sposób że tekst który nie mieści się w komórce będzie pokazany tak jak właśnie w power query czyli z tymi kropkami (...) oczywiście po zapisaniu w excelu ?
Hi Bill, very cool stuff and good to know about the precedence in record concatenation. BTW: Are you aware of the Record.TransformFields-function? It iterates through each field of the record and does some transformation on it just like the List.Transform-function. So this case could then be solved like this: Table.FromRecords(Table.AddColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Custom", each Record.TransformFields(_, List.Transform(Record.FieldNames(_), (y)=>{y, (x)=>if x=null then null else y})))[Custom]) (which is actually a tiny bit shorter than yours ;-) )
Actually, this is the first use case for Table.TransformRows that I've come across :-) : Table.FromRecords(Table.TransformRows( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], each Record.TransformFields(_, List.Transform(Record.FieldNames(_), (y)=>{y, (x)=>if x=null then null else y}))))
Such a fantastic transformations, Imke!!! But... one thing....look what you get in "Name" column....One more transformation is needed. Instead of Record.FieldNames(_) should be List.Skip(Record.FieldNames(_),1) ;-)))) Yes I know Record.TransformFields and sometimes i use this function. I made this one-step query just for fun becouse i saw Oz's video and on the comments site there were a few solutions looked like the contest for the shortest query. I decided to write the one-step query in a way that it would be the easiest to read for those who know a little M language. This is not the best query, this is not optimal query... this is just the fun with M. After I publish it, to my surprise, I got many requests to explain how does this one-step query works, so i made this video. That's all. :-))) Thanks for watching and for kind words :))
This is the best Video I have ever seen from you because even though you spoken in Polish, I understood you perfectly - your movement of the cursor, your use of the diagram on the second sheet and your highlighting the parts of the formula for emphasis was simply amazing!!! It was so good that I felt as if I DID understand Polish. But at the end, are you sure that Mike took all the classes!?!? Thank you very much, Bill "Power Query Artists and Poet" Szysz!!!
Thanks for the motivating comment !!! I will try to make future videos in this way :-))
Thank for watching, Mike.
You are the legend of excel (to be sure... in Polish, to be a legend is a compliment) :-))
The video was really amazing because I followed you the whole way!!!! In Polish or English - you are a legend in Excel and Power Query!!!! I hope you became an Excel MVP sometime. I mentioned you to a few Microsoft people, and just said that I have not seen solutions like the ones you create from any one else. For me, you are THE smartest Power Query person. Plus, I get the added benefit of learning polish too : )
Thank you, Mike. :-))
jeden z największych guru z excela:D
Dzięki za miłe słowa, choć chyba trochę na wyrost :-)) Powiedzmy, że coś tam wiem o Excelu i dodatkach Power :-)
@@BillSzysz1 Chciałbym "coś tam wiedzieć" jak Pan o excelu i pq
Dzięki Bill za tłumaczenie!
Muszę się jeszcze dużo nauczyć, żeby osiągnąć taki poziom znajomości Power Query ;)
Dzięki za oglądanie :-)
W PQ na początek zapomnijmy myśleć po "excelowemu"....to nie pomaga.... ale akurat w tym konkretnym przypadku, budowanie zapytania odbywa się prawie tak samo jak budowanie formuły w excelu.
Jednak tak się dzieje tylko niekiedy.
Na prawdę po mistrzowsku, teraz ograniczanie liczby kroków zapytania będzie bardziej intuicyjne. Dziękuję Bill.
Thanks for this! Very sophisticated. I'm going to study the file you sent me.
Thanks Oz :-) I tried to explain how it works but i am not sure if I succeeded
Dobre jak zwykle! :)
Dzięki Piotr :-))
SZTOS!
Jestem pod wrażeniem! Bill, po prostu cudo! Jeszcze wieeeele power-querowej pracy przeze mną, żeby robić w tym narzędziu choćby ułamek tego, co Ty :). Dzięki za film!
Dzięki za obejrzenie filmu :-))
Skoro uważasz, że sporo drogi przed Tobą w PQ to znaczy, że wiele też radości z odkrywania fajnych sposobów i sposobików ;-)))
Oj tak, zdecydowanie! I mam się od kogo uczyć i podglądać świetne rozwiązania :)
Hi Bill,
Wow, so elegant! Thank you very much for sharing, As Oz, I'm downloading and will study your file.
Thanks Eric :-))
I can find the 100 Thumbs Up Button!!!
Thanks Bill! I was wondering what is your idea, and then seen this records concatenation trick - awesome! Masterpiece!
As one of the fellows says, "you made complex transformation, and then Bill come into the scene with just one step"
Thanks Maxim for your kind words. I am not as good as you think :-))
Ale super
Dzięki :-)
Niedościgniony Mistrz PQ ! Wirtuozeria konstruowania zapytań :)
Tego się nie idzie nauczyć - to trzeba mieć we krwi
Akurat teraz to mam we krwi cosik innego :-)))
Dzięki za miłe słowa (acz chyba jednak przesadzone "trochę" ) :-)))
Sam się przecież dobrze kręcisz w obszarze PQ :-))
Witam mam pytanie czy można przekształcić w power query np tabelę ale w ten sposób że tekst który nie mieści się w komórce będzie pokazany tak jak właśnie w power query czyli z tymi kropkami (...) oczywiście po zapisaniu w excelu ?
Everyone speaks highly of you. Do you have an English channel?
Hello Bill.
It's said that you help in bigger and smaller excel projects.
I've sent you LinkedIn invitation.
Hi Bill, very cool stuff and good to know about the precedence in record concatenation.
BTW: Are you aware of the Record.TransformFields-function? It iterates through each field of the record and does some transformation on it just like the List.Transform-function. So this case could then be solved like this:
Table.FromRecords(Table.AddColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Custom", each Record.TransformFields(_, List.Transform(Record.FieldNames(_), (y)=>{y, (x)=>if x=null then null else y})))[Custom])
(which is actually a tiny bit shorter than yours ;-) )
Actually, this is the first use case for Table.TransformRows that I've come across :-) :
Table.FromRecords(Table.TransformRows( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], each Record.TransformFields(_, List.Transform(Record.FieldNames(_), (y)=>{y, (x)=>if x=null then null else y}))))
Such a fantastic transformations, Imke!!!
But... one thing....look what you get in "Name" column....One more transformation is needed. Instead of Record.FieldNames(_) should be List.Skip(Record.FieldNames(_),1) ;-))))
Yes I know Record.TransformFields and sometimes i use this function.
I made this one-step query just for fun becouse i saw Oz's video and on the comments site there were a few solutions looked like the contest for the shortest query.
I decided to write the one-step query in a way that it would be the easiest to read for those who know a little M language. This is not the best query, this is not optimal query... this is just the fun with M.
After I publish it, to my surprise, I got many requests to explain how does this one-step query works, so i made this video. That's all. :-)))
Thanks for watching and for kind words :))
One more... without additional transformation you should replace this part
if x=null then null else y
to this one
if x="x" then y else x
:-))
Thanks Bill, yes I've missed that :-)