The Algebra Teacher Powers to B.I.

  A week ago in my last training, I was demonstrating how to get rid of duplicate records when making a dimension table.   One of the people in attendance asked is there a way to get rid of duplicates but keep the most recent duplicate record based on a date column.   I thought, “Of Course!”.   All we need to do is use the Query editor, do a sort on the date column, and then get rid of duplicates.   Unbeknownst to me, it didn’t work.   It kept the oldest record and not the newest.    The reason being after doing some research is that the Power Query editor will make determinations if it can do some of your steps in a different order and still give you the same result while producing the quickest data load.   Normally, that is what an ideal Power BI user wants, but in this case, we need the sort to happen before the remove duplicates step.   Thus, after research comes in the function Table.Buffer to modify our sort code.    Take...

 In last week's blog/video, I showcased how you can use the patch function to create a record without using the preset forms in Power Apps.   In this week's blog/video, I go a little further with it. One problem that arises with the Patch function is that the old values will still be populated in your input controls.  So I want to show you how to wipe those out and set them back to the default blank values.  We will use variables and the UpdateContext command attached to the OnVisible property of the screen where the input controls are. To go even further this week, I also want to show you how to enable/disable a button.  I've come across scenarios where we don't want the user to hit the submit button on a form or a patch command unless there is data in the input controls.  So I will walk you throw how to set up that action as well this week using the If and IsBlank functions.

 In Power Apps, forms are great to use to submit data to be recorded in your data source.  They do not take long to set up and the functions used to submit the data are fairly simple.  This simplicity, however, can come at a cost.  The cost of using a form is you don’t have a lot of design control in terms of layout and design.  If you don’t like the rigid structure of forms and want more freedom, then I’ve got the fix for you.  You need to become acquainted with the Patch function.   The Patch function allows you to update or create a new record in your data source.   The Patch function requires you to identify your data source, decide if you want to update or create a record, and then point to your controls on the app that contains the data you are submitting.   The coding is a little more involved compared to SubmitForm(FormName) that you use on forms.   The payoff, though, for learning a little more advanced code is you get compl...

  If you have geographical data in your data source, using a map visual at some point in your Power BI report is almost a given.   Sometimes, however, the map doesn’t produce the results you were looking for.   For example, Fort Wainwright, Alaska might show up in India or not show up at all.   If you have ever had trouble with your maps not displaying what you know is right let me give you a few tips and tricks.  1)        When you use a map you will have to change the category settings of your column in order for Power BI to know what kind of geography this column represents.   Is it a zip code, a city, a city and a state, etc.? 2)        If you use abbreviations for your states, countries, territories, etc. make sure Power BI doesn’t have to guess what part of the world is using that abbreviation.   To avoid confusion, you want to have in your data source the full name of your state, country, territory,...

While doing Power Apps training with Pragmatic Works, I usually have a common request after students learn that they can delete records.  Someone will usually state they like the usability of the delete function, but are scared to put a delete button on the screen.  Their concern is someone will hit it by mistake and then the record will be gone forever.   There are a few ways to fix this issue.    I did a blog/video several weeks ago that shows how to make a pop-up screen to have your end-user click twice before the record is really deleted.  You can check it out here Deleting Is Scary .  In this week's blog/video, I will show you how you can delete a record from the original source, but then back it up in another data source. To achieve our solution today we will need to meet the following objectives: *   Have two separate data sources that will contain a table/list with the same number of columns, column headers, data types, etc. * Dis...