WOW! Just mind blowing. Never knew that you could use Records instead of the let statement in that context. I remember you did a video before, where you did crazy transformations inside the nested tables using the let statement. Yes Records are better. Learned 2 new fns List.Repeat & List.PositionOf. Being the noob I am, I had to watch this amazing video a couple of times to grasp its real beauty.
The POWR of RECORDS, Amazing! I learnt a new function List.Repeat. I mostly watch your to understand the problem and try myself then continue to watch how you tackle it, then learn something new always. Here is my solution (my 2 cents): let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], threshhold = 120, t_group = Table.Group(Source, {"PortfolioCode"}, { {"Tbl", each
let t = _, t_index = Table.AddIndexColumn( t , "Index", 0 , 1), min_no = List.Min(Table.SelectRows( t_index , each [Cumulative investment Amount] > threshhold )[Index]), t_newcol = Table.AddColumn( t_index , "Col", each if [Cumulative investment Amount] > threshhold then [Index] - min_no+1 else 0)
That is brilliant, and I'm still working on understanding how it all fits together, but I did have a go myself, this did mean having a unique date or index column, then Table.SelectRows(
Hi Sensai, could you please let me know whether this is possible in pq I have two tables in powerquery One table -Custreview ProductId, customer-comments 100, it sucks 102, this is amazing Second table -custmoderate ProductId, lookupword, replaceword 100, sucks, not good 100,it, this 102,amazing,exemplary From Custreview table match Product Id with custmoderate table; if matched; take customer comments field from Custreview and replace lookupword with replaceword. One Product Id can have 100's of word that need to be replaced. Please guide me how to do it in powerquery.
I think the first step needs to be, "Make sure your data is sorted by date, in ascending order." If the data isn't presorted, the code won't work as expected.
Amazing 👍. Thanks James and looking forward to more and more videos from you ❤
WOW! Just mind blowing. Never knew that you could use Records instead of the let statement in that context. I remember you did a video before, where you did crazy transformations inside the nested tables using the let statement.
Yes Records are better.
Learned 2 new fns List.Repeat & List.PositionOf. Being the noob I am, I had to watch this amazing video a couple of times to grasp its real beauty.
aaah man this is awesome!
You are just amazing man, concepts and solutions you bring are out of the box...
The POWR of RECORDS, Amazing! I learnt a new function List.Repeat.
I mostly watch your to understand the problem and try myself then continue to watch how you tackle it, then learn something new always.
Here is my solution (my 2 cents):
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
threshhold = 120,
t_group = Table.Group(Source, {"PortfolioCode"},
{
{"Tbl", each
let
t = _,
t_index = Table.AddIndexColumn( t , "Index", 0 , 1),
min_no = List.Min(Table.SelectRows( t_index , each [Cumulative investment Amount] > threshhold )[Index]),
t_newcol = Table.AddColumn( t_index , "Col", each if [Cumulative investment Amount] > threshhold then [Index] - min_no+1 else 0)
in
t_newcol
}
}
),
t_removecol = Table.RemoveColumns( t_group , "PortfolioCode" ),
t_expanded = Table.ExpandTableColumn(t_removecol, "Tbl",List.RemoveItems(Table.ColumnNames( t_removecol[Tbl]{0} ), {"Index"} ) )
in
t_expanded
i love this ! this is a great solution! Nested Let!
That is brilliant, and I'm still working on understanding how it all fits together, but I did have a go myself,
this did mean having a unique date or index column, then Table.SelectRows(
Another great video, would like to hear the real-world situation that prompted this. Seems like a strange thing to need to accomplish.
amazing video
So in a record you squeeze 2 integers, 1 list and 1 table... Nice! I want you do it (same outcome) with nested LET, to really make a point.
Looks interesting but I'm going to have to work through that quite a few times to understand what is happening in each step.
Hi Sensai, could you please let me know whether this is possible in pq
I have two tables in powerquery
One table -Custreview
ProductId, customer-comments
100, it sucks
102, this is amazing
Second table -custmoderate
ProductId, lookupword, replaceword
100, sucks, not good
100,it, this
102,amazing,exemplary
From Custreview table match Product Id with custmoderate table; if matched; take customer comments field from Custreview and replace lookupword with replaceword. One Product Id can have 100's of word that need to be replaced.
Please guide me how to do it in powerquery.
How can you create a library of custom functions?
it's a good idea. i've seen some dudes create these online and make them available. maybe is hould start doing that also.
I think the first step needs to be, "Make sure your data is sorted by date, in ascending order." If the data isn't presorted, the code won't work as expected.
true that! my bad!