Your opinion for my second query

[Dico]

Hi,

I'm taking my project step by step,
This is the next step from my previous threads
I had collected on this forum some good ideas that I adapted to the requests and thanks again to the participants.

Here's a new video.

Nothing difficult so far, but it gets more complicated for me after this two simple queries.
But your remarks or optimizations are welcome


Otherwise, l will continue and open a new discussion probably at the end of the Week for the next step.

Thank you in advance

 

[AliGW]

What is the query? Please state what you are looking for help with. I’m not going to watch a video to find out, sorry, and nor should anyone have to.

 

[Dico]

Hi AliGW,

I'm not asking for help, I'm asking to present the continuation of my project and possibly some remarks or tips in the "Base_Allowance" query.

Those of you who have read my previous threads will certainly understand what I'm getting at.

But thank you anyway.

 

[NormS]

Dico, the only problem I have with your video is that it's in French, which I last studied in school more than 40 years ago! By the way, what are you using to create the video?

At one point in your query you create a conditional column, Personalise, which negates some of the values in the Amount column. You then delete the Amount column. This blog post by Imke Feldmann

https://www.thebiccountant.com/2017...er-column-in-powerbi-and-powerquery-in-excel/

shows how to do that negation directly in the Amount column using Replace Value, like this:

= Table.ReplaceValue(#"Data développé",each [Amount],each if [PK]=20 then [Amount] else -1*[Amount],Replacer.ReplaceValue,{"Amount"})

 

[Dico]

Hi, NormS,

Thank you, I really appreciate your help and the relevance of your answers.
I'm going to apply this solution which will allow me to have fewer steps

Concerning the video, I use Adobe Captivate (paid software)
But, there are free softwares that make very good videos too (Camstudio for example).

Otherwise, you're right, the only problem is that my office isn't in US…
I think I need a US version or use VBA code to change the language.

The good thing is that the ribbon commands in Excel and Power query are in the same place between the different countries.

For my part, I also learned a lot by watching YouTube videos in Russian or Polish :smile:

By using Office for a long time, I quickly became familiar with YouTube videos on Excel/Power Query.


@ Hi AliGW, your comments are always welcome,

This is an ongoing project and I will open further discussions here,
The requests will become progressively more complicated for me anyway.

I'm gonna need everyone, on one of the best forum on Power query I think

Otherwise, the good news is that my videos are automatically deleted after 7 days.:smile:

Thanks again to you

Have a nice day.

 

[AliGW]

I really don't see the need for you to open multiple threads on your project. Just stick to ONE thread and add to it when you want to talk some more - those who are interested will see that you have posted to it again.

 

[Dico]

AliGW,

Indeed, it would be simpler and more logical by opening several discussions,
I thought I would target as many members as possible each time.
My positions did seem a little fuzzy so far, but I've been able to get some good answers and some help.
I also thought I would ask more specific questions soon with further discussions.

But maybe you're right, I'm following up on that thread but with new videos…:smile:
A video is better than 1000 explanations

Have a nice day.

 

[AliGW]

As long as it’s in English! There aren’t many of us who are fluent in French.

 

[Dico]

Hi everyone,

Here is the continuation of my project with a Power Quey version in English (see video here)
This is the 2nd part out of normally 3 to complete the project.

In this 2[SUP]nd[/SUP] part, I've inserted a lot of tables and parameters related to the 3rd query "qy_SLR".

I would like please to improve the following points:

- Delete the message "Be careful! Parts of your document...". The one appears every time I make a recording

- how to adapt the VBA code to the "qy_SLR" query

I replaced: ThisWorkbook.Connections("Query - Special_Allowance_Paid").Refresh
By ThisWorkbook.Connections("Query - qy_SLR").Refresh but it doesn't work

- The last step of the query "qy_SLR" is problematic when I want to remove from the selection, a column "Name" of the table "tbl_Category".
I don't know where to put the "MissingField.Ignore" feature.

I managed to put in the previous steps "Reordered Columns" and "Reordered Columns1" but not in "Rounded Off".

- I would also like to sum up the columns in "Total" according to the "Yes/No" selection in the "Calculate" column of the "tbl_Category" table.
How do I add this step?

Otherwise, in general, what could be improved ?
As you may have noticed on the video, the refresh rate is starting to be a little slow.

Thank you in advance for your help

 

[Dico]

- how to adapt the VBA code to the "qy_SLR" query

I replaced: ThisWorkbook.Connections("Query - Special_Allowance_Paid").Refresh
By ThisWorkbook.Connections("Requête - qy_SLR").Refresh but it doesn't work

Sorry,

I found for this VBA point :

Silly me, I didn't think my file was in English anymore. I left it in French

The correct instruction was :

ThisWorkbook.Connections("Query - qy_SLR")

 

[NormS]

Hi everyone,

I would like please to improve the following points:

- Delete the message "Be careful! Parts of your document...". The one appears every time I make a recording

Don't know for sure but I think you get this warning because you have folder paths like this

C:\Users\...

in your worksheets and/or queries.

 

[NormS]

Hi everyone,

- The last step of the query "qy_SLR" is problematic when I want to remove from the selection, a column "Name" of the table "tbl_Category".
I don't know where to put the "MissingField.Ignore" feature.

I managed to put in the previous steps "Reordered Columns" and "Reordered Columns1" but not in "Rounded Off".

The documentation here:

https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns

suggests you can change the Rounded Off step to this

= Table.TransformColumns(#"Added Custom3",{{"Name 5", each Number.Round(_, 2), type number}, {"Name 7", each Number.Round(_, 2), type number}, {"Name 6", each Number.Round(_, 2), type number}, {"Name 8", each Number.Round(_, 2), type number}, {"Name 9", each Number.Round(_, 2), type number}, {"Name 10", each Number.Round(_, 2), type number}}, null, MissingField.Ignore)

 

[Dico]

Hi NormS :smile:,

This message appeared after inspecting and cleaning up some information in the document (see image)
If I delete 100% of the "confidential" information, I lose all the queries and links in my file.

And you're right that's one of the reasons when you mentioned the path

Otherwise, this point is not important, I think I should find a VBA code on the Net for not to see this message displayed

Have a nice day.

 

[Dico]

The documentation here:

https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns

suggests you can change the Rounded Off step to this

= Table.TransformColumns(#"Added Custom3",{{"Name 5", each Number.Round(_, 2), type number}, {"Name 7", each Number.Round(_, 2), type number}, {"Name 6", each Number.Round(_, 2), type number}, {"Name 8", each Number.Round(_, 2), type number}, {"Name 9", each Number.Round(_, 2), type number}, {"Name 10", each Number.Round(_, 2), type number}}, null, MissingField.Ignore)

NormS,

I had searched on the Microsoft France page on this link
He has almost no explanation !!
https://docs.microsoft.com/fr-fr/powerquery-m/missingfield-ignore

There is more information on your link
Otherwise, thank you very much, so this point is solved.

I'll come back to the last part of the Project this Weekend hoping to get help on the last problem of my thread #8
I'm open to any other method for performance and speed,

Have a nice day.

 

[Dico]

Hi everyone,
Here is the last part of my project with a video for the demonstration

- I would like to know if it is possible to get this result directly from "qy_SLR" without creating "qy_PLE" ?
- How to replace all values by null for all columns between the comumn "Code" column and "Sum A+B- Allow Paid" knowing that the number of columns can be variable (see at 3:53)

I deliberately did not cut the sequences where PQ recalculâtes to show that it takes some time to update
- Otherwise, in general, can I still optimize the performance knowing my real file that my folder contains all the files for the year has more rows ?

Thank you in advance