Split a Power BI (PBIX) File into a Stand-Alone Dataset

Sometimes you just need the steps.  This is one of those times.  This enables you to use the same dataset across multiple reports, further enabling you to update the model continually across multiple reports without having to access each report.  Obviously, you would want to validate that changes don't break the other reports. 

The essential points of this video are to ...

1. Build the model and report.
2. Save Report-1
3. Save As Report-2
4. In Report-2
1. Add a new blank tab
2. Delete all other tabs
3. Publish
5. In Power BI Service,
1. Delete Report-2, but not the dataset
6. In Report-1,
1. Transform
2. Delete the model and all queries
3. Close & Apply
4. Save
5. Get Data
1. Choose Report-2 dataset in Power BI Service

Thinking about this further though, I still have one question.  What performance difference is there, if any, between doing this and simply having the originating report and dataset in place, and simply attaching to that originating dataset as needed for other reports?  All of these dependencies look the same in the Power BI Service, whether split or not.

DNC Data Thieves

It has become abundantly clear to me that the Democratic National Committee (DNC) are thieves of personal data.  At minimum, they are complicit in the thievery through their use of stolen data.

Since my phone number was reported as being compromised in October 2023 the number of spam messages and calls has moved from zero to more than I care to discuss.

All of these spam messages and calls are from the same people, those representing the DNC, Democrats, or associated action groups.

That is not the way to win my vote.

Indeed, it is very likely to have the opposite of the intended effect.

====================================================

09.11.2024 Update

It stopped for a while, but the proof reared its ugly head again.

The only spam seems to come from the DNC and their supporters.

Dog Bites

Out on the usual morning walk, I got jumped by some old hound dog a little old lady was walking.  It was really more of a sneak attack.  He got ahold of my shirt and pulled away, ruining a one of my long-time favorites.  This shirt has been holding on for more than 20 years.

Scraping Cement Dirt

Power washing the patio uncovered exactly how filthy it had become.  It is kind of interesting, since it did not look quite as dirty as it truly was, until I started.  Seems I may have to do the walkway and steps into the barn too.

Merge and Append in Power Query

The process for Merge and Append are essentially the same.  This walks through Merge. 

Merge = Combines two or more files based on one or more matching columns so that their shared data appears on one row.  In this example, starting with 1085 rows, and another with 1082 rows, we will end up with 1085 rows. 

Append = Simply combines (add rows from one to another) two or more files and matches columns where they have the exact same name.  In this example, starting with 1085 rows, and another with 1082 rows, we would end up with 2167 rows. 

1. In Excel, 
1. Open a Blank Workbook 
2. Choose the Data menu 
   
   
   
   
   
1. Choose Get Data 
1. > from File 
2. > from Excel Workbook 
1. Select a file from the window that opens 
2. In the subsequent window, click the appropriate tab from that Excel file
   
   
   
   
   
3. Choose Transform Data
1. Power Query Editor window opens 
   
   
   
   
   
2. In Power Query Editor, in the New Query section 
   
   
   
   
   
1. Choose New Source  
1. > File 
2. > Excel Workbook 
1. Select a file from the window that opens 
1. In the subsequent window, click the appropriate tab from that Excel file, and click OK 
2. Two reports now appear in the Queries pane (left) 
   
   
   
   
   
2. In the Combine section,  
1. Choose the drop down next to “Merge Queries” 
2. Choose Merge Queries as New
   
   
   
   
   
1. A new Merge window opens, with one of the Excel files already chosen 
   
   
   
   
   
2. Choose the other Excel file from the drop-down in the middle 
   
   
   
   
   
   
3. Begin choosing which data columns to match on 
1. Click the column name from the top sample, then click the matching column name in the bottom sample.  Both will highlight when selected.
2. Choose columns that contain the exact same information.  Both tables should contain matches, though not all will match.  In this example, the match is between “IDF Form ID” and “Individual Data form ID,” because they will contain the same data. 
1. If matching on more than one column, CTRL+Click the second column 
   
   
   
   
   
4. Choose the Join Kind at the bottom. 
1. If unsure, choose “Full Outer” to keep all data from both Excel sheets
5. Click OK 
1. The Merge window disappears, and a new table appears in the Queries in the left panel.  This is file containing the merged files. 
   
   
   
   
   
6. Click Close & Load 
1. The window returns to the initial Excel Workbook and loads the merged files. 
3. In the Excel Workbook, the right panel will highlight which file is currently being viewed.
   
   
   
   
   

Joining Spreadsheets in Excel

Power Query, which Power BI relies upon for data load and transformation first appeared in Excel.  It enables one to automate changes to data sets through filters and other modifications each and every time data is loaded to a spreadsheet dependent upon a Power Query.

It also enables a user to join two separate spreadsheets as if they were working with tables in a database.

The following walks through the joining two tables in Excel, using Power Query.

The process for Merge and Append are essentially the same.  These steps relate to Merge.

Merge = Combines two or more files based on one or more matching columns so that their shared data appears on one row.  In this example, starting with 1085 rows, and another with 1082 rows, we will end up with 1085 rows.

Append = Simply combines (add rows from one to another) two or more files and matches columns where they have the exact same name.  In this example, starting with 1085 rows, and another with 1082 rows, we would end up with 2167 rows

1) In Excel

a) Open a Blank Workbook

b) Choose the Data menu

 i) Choose Get Data

(1) > from File

(2) > from Excel Workbook

(a) Select a file from the window that opens

(b) In the subsequent window, click the appropriate tab from that Excel file

 (c) Choose Transform Data 

(i) Power Query Editor window opens

  2) In Power Query Editor, in the New Query section 

a) Choose New Source 

i) > File

ii) > Excel Workbook

(1) Select a file from the window that opens

(a) In the subsequent window, click the appropriate tab from that Excel file, and click OK

(b) Two reports now appear in the Queries pane (left)

b) In the Combine section, 

i) Choose the drop down next to “Merge Queries”

ii) Choose Merge Queries as New

(1) A new Merge window opens, with one of the Excel files already chosen

(2) Choose the other Excel file from the drop-down in the middle

(3) Begin choosing which data columns to match on

(a) Click the column name from the top sample, then click the matching column name in the bottom sample.  Both are highlighted when selected.

(i)  Choose columns that contain the exact same information.  Both tables should contain matches, though not all will match.  In this example, the match is between “IDF Form ID” and “Individual Data form ID,” because they will contain the same data.

1. If matching more than one column, CTR+Click the second column

(4) Choose the Join Kind at the bottom.

(a) If unsure, choose “Full Outer” to keep all data from both Excel sheets

(5) Click OK

(a) The Merge window disappears, and a new table appears in the Queries in the left panel.  This is file containing the merged files.

 

(6) Click Close & Load

(a) The window returns to the initial Excel Workbook and loads the merged files.

3) In the Excel Workbook, the right panel will highlight which file is currently being viewed

That is all there is to it!  Happy Joining!

Liberty Photo Gig Cow

While doing photos of a local maintenance crew and their new trucks I stumbled upon one of the cows from the Cow Parade.  They have since been replaced by hearts in an effort to rid Kansas City of that old cow town image.  This particular cow lurks in the backyard of an old manor house in Liberty, apparently well groomed and maintained, in spite of its age.

Power BI Scorecard Cheat Sheet

Asked to look into the Scorecard feature, I drew up a little PowerPoint Cheat Sheet for the Cs & Vs to help them along in understanding how simple it all really is.  There was a little video attached at the end, walking through all of these too.  It has info in it that really cannot be shared though, so we will leave that out of here.  The whole thing really is much more simple than I even thought too.

Scorecards in Power BI

In plain words, the Metrics Scorecard feature enables you to ...

• Track any metric and be notified of changes outside of your specified parameters.
• Does not have to be a Power BI report metric!  Make your own!
• Track any Power BI value as a metric and be notified of changes outside of your specified parameters.
• Track metrics AND sub-metrics
• One Scorecard can contain multiple seemingly unrelated metrics and sub-metrics
• Multiple Scorecards can contain multiple seemingly unrelated metrics and sub-metrics
• Share your scorecard(s) with those you choose.
• Share your metric(s) with those you choose.
• Enable "Check-Ins" for others to mark their regular status or reason for changes outside of parameters.
• Completely private in your space, unless you choose to share it out.

Requirements: Power BI license, which provides access to https://app.powerbi.com, where the scorecard, metrics, and sub-metrics are created and stored. 

. . .

Below are the slides created for the original presentation, which outlines the Score Card feature in Power BI (online), along with a little no sound video walkthrough.

. . .

. . .

learn more 

Create scorecards and manual metrics in Power BI

Non-Profit AI

Interesting story reviewing the obvious points in getting Artificial Intelligence, or as I like to call it Automated Interface, up and running. It is not something you just slap together.

Unlocking AI’s Potential for Nonprofits Requires Data Transformation First (aibusiness.com)

Acrobatic Squirrels

While out on a walk this morning, I noticed this guy hanging upside down from a bird feeder, stuffing himself with all the snacks he could.

Late Summer Kids

Late summer, early evening, kids hanging on the cattle fence, cheering on their favorite at a truck and tractor pull.

First Pumpkin of 2024

Noticed our first pumpkin of the year plumping up on the vine.  There are about three or four others that have also just now started to make themselves visible.  Hopefully, we have more before it is all over.

A new Crestview

Got a glimpse of the new Crestview last night, sitting pretty and waiting patiently for the first day of school, half wishing I were there for it.  So many mixed feelings.  Part of me wants to go back to teaching.  It gave work some sort of meaning.  The other half of me asks, "Are you out of your bleeping mind?"

The work was easy, but never ending and it only left room to do things outside of the school year.  I suppose that is not a whole lot different from only having space space to do things on vacation, but much of it was meaningless "professional development."

Caught in the Rain

Got a good soaking half way through the evening one-mile trek walking the dog turned.  It was not so bad though, warm enough rain and just warm enough temperature enabled a clean and fresh sort of feeling.  The dog did not seem to care, though she never does.

Google Password Risk

This new little Google feature rolled out over the weekend for Android. It is probably as secure as any other thing they produce, although it seems they are enabling more risk than they really should. 

This must be for those folks that like to share their credit card with their children too.  I am going to pass on this one.  Use your own password.