Power Query - Remove Text Between Delimiters Reusable Custom Function - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
liam
I have a power query and I have it loading to a table on a sheet.-
I have a PivotTable looking at this Table.-
In hindsight I should have loaded the power query as a connection only and pointed the PivotTable to the power query.-
I can load the query as an connection only, but I can-t change the Data Source of the PivotTable to the power query.-
I have changed the Data Source in the past form one table to another, and I think I gone form one power query to another.-
I have done a lot of formatting to that PivotTable, so I don-t really want to redo all the work.-
Any Idea how to change a PivotTable Data Source form a table to a Power Query?
reply
I have a power query and I have it loading to a table on a sheet.-
I have a PivotTable looking at this Table.-
In hindsight I should have loaded the power query as a connection only and pointed the PivotTable to the power query.-
I can load the query as an connection only, but I can-t change the Data Source of the PivotTable to the power query.-
I have changed the Data Source in the past form one table to another, and I think I gone form one power query to another.-
I have done a lot of formatting to that PivotTable, so I don-t really want to redo all the work.-
Any Idea how to change a PivotTable Data Source form a table to a Power Query?
reply
Эльмар
Hi Phil. First of all, thanks a lot for great content. Very helpful.
Second: do you know if it is possible to make only the Y axis min/max bounds dynamic? I have a pivot chart consisting of multiple lines. X axis = 12 months. Y axis = values (EUR). When I click on a slicer, lines are adjusting fine. However, because min/max bounds of Y axis are static, lines look weird (too close to each other in some cases, and all on the bottom of the chart in other cases).
reply
Hi Phil. First of all, thanks a lot for great content. Very helpful.
Second: do you know if it is possible to make only the Y axis min/max bounds dynamic? I have a pivot chart consisting of multiple lines. X axis = 12 months. Y axis = values (EUR). When I click on a slicer, lines are adjusting fine. However, because min/max bounds of Y axis are static, lines look weird (too close to each other in some cases, and all on the bottom of the chart in other cases).
reply
Rico
Great video Phil. I do love a PQ custom function. What was the purpose of the nested let? Could you not just have the parameters in the opening line (line 1) of the code, with just a single let (final row being in L3, as opposed to source)? Is it just a best practice type thing?
Edit: what I mean is if you remove steps 1, 9 and 10 from your code, and the -Source = - in line 2, is the function affected?
reply
Great video Phil. I do love a PQ custom function. What was the purpose of the nested let? Could you not just have the parameters in the opening line (line 1) of the code, with just a single let (final row being in L3, as opposed to source)? Is it just a best practice type thing?
Edit: what I mean is if you remove steps 1, 9 and 10 from your code, and the -Source = - in line 2, is the function affected?
reply
Jack
Outstanding tutorial! Just one minor point, the -Cleaned Data- output when run with -True- does not contain the delimiters for the final entry of the list. In the function, change the line for L3 to be: L3 = if KeepDelim then Text.Combine(L2, Text.Combine(-LeftDelim, RightDelim-)) & LeftDelim & RightDelim else Text.Combine(L2)
reply
Outstanding tutorial! Just one minor point, the -Cleaned Data- output when run with -True- does not contain the delimiters for the final entry of the list. In the function, change the line for L3 to be: L3 = if KeepDelim then Text.Combine(L2, Text.Combine(-LeftDelim, RightDelim-)) & LeftDelim & RightDelim else Text.Combine(L2)
reply
Ron
Another great tutorial. One question, when you start typing in the Custom Column New Formula box you get a list of functions show up and gets filtered as you type. When I start typing nothing comes up. How do I turn this on?
reply
Another great tutorial. One question, when you start typing in the Custom Column New Formula box you get a list of functions show up and gets filtered as you type. When I start typing nothing comes up. How do I turn this on?
reply
Beth
Can functions be saved to your normal.dot like custom VBA functions? Love these coding examples Phil shares, and hoping there is an easier way to share functions across workbooks!
reply
Can functions be saved to your normal.dot like custom VBA functions? Love these coding examples Phil shares, and hoping there is an easier way to share functions across workbooks!
reply
Wayne
Awesome Mynda and Phil! Writing custom functions takes some practice for sure. Thanks for the thorough explanation and providing the sample file to follow along :)) Thumbs up!!
reply
Awesome Mynda and Phil! Writing custom functions takes some practice for sure. Thanks for the thorough explanation and providing the sample file to follow along :)) Thumbs up!!
reply
Lucky
Great tutorials. You're showing us the full potentials of PQ as a programmable tool. Please, keep these custom functions coming. This is more like the VBA of Excel for PQ.
reply
Great tutorials. You're showing us the full potentials of PQ as a programmable tool. Please, keep these custom functions coming. This is more like the VBA of Excel for PQ.
reply
jp
Thanks for the tutorial! One question though, how do I modify the function so that if there are no delimiters, it will retain the original value?
reply
Thanks for the tutorial! One question though, how do I modify the function so that if there are no delimiters, it will retain the original value?
reply
Geert
I may be missing the point here, but solving this takes only three steps in PQ using the interface.
See the file I sent you. :-)
reply
I may be missing the point here, but solving this takes only three steps in PQ using the interface.
See the file I sent you. :-)
reply
Add a review, comment
Other channel videos
