www.thebiccountant.com Open in urlscan Pro
81.169.145.79  Public Scan

Form analysis
4 forms found in the DOM

GET https://www.thebiccountant.com/

<form method="get" id="searchform" action="https://www.thebiccountant.com/">
  <input type="text" class="field" name="s" id="s" placeholder="Search ...">
  <input type="submit" class="submit" name="submit" id="searchsubmit" value="Search">
</form>

Name: login — POST https://www.thebiccountant.com/login/

<form name="login" action="https://www.thebiccountant.com/login/" method="post">
  <div class="tml-field-wrap tml-log-wrap">
    <label class="tml-label" for="user_login">Email</label>
    <input name="log" type="text" value="" id="user_login" autocapitalize="off" class="tml-field">
  </div>
  <div class="tml-field-wrap tml-pwd-wrap">
    <label class="tml-label" for="user_pass">Password</label>
    <input name="pwd" type="password" value="" id="user_pass" class="tml-field">
  </div>
  <div class="tml-field-wrap tml-rememberme-wrap">
    <input name="rememberme" type="checkbox" value="forever" id="rememberme" class="tml-checkbox">
    <label class="tml-label" for="rememberme">Remember Me</label>
  </div>
  <div class="tml-field-wrap tml-submit-wrap">
    <button name="submit" type="submit" class="tml-button">Log In</button>
  </div>
  <input name="redirect_to" type="hidden" value="/tools-power-bi-cleaner/">
</form>

<form id="jp-carousel-comment-form">
  <label for="jp-carousel-comment-form-comment-field" class="screen-reader-text">Write a Comment...</label>
  <textarea name="comment" class="jp-carousel-comment-form-field jp-carousel-comment-form-textarea" id="jp-carousel-comment-form-comment-field" placeholder="Write a Comment..."></textarea>
  <div id="jp-carousel-comment-form-submit-and-info-wrapper">
    <div id="jp-carousel-comment-form-commenting-as">
      <fieldset>
        <label for="jp-carousel-comment-form-email-field">Email (Required)</label>
        <input type="text" name="email" class="jp-carousel-comment-form-field jp-carousel-comment-form-text-field" id="jp-carousel-comment-form-email-field">
      </fieldset>
      <fieldset>
        <label for="jp-carousel-comment-form-author-field">Name (Required)</label>
        <input type="text" name="author" class="jp-carousel-comment-form-field jp-carousel-comment-form-text-field" id="jp-carousel-comment-form-author-field">
      </fieldset>
      <fieldset>
        <label for="jp-carousel-comment-form-url-field">Website</label>
        <input type="text" name="url" class="jp-carousel-comment-form-field jp-carousel-comment-form-text-field" id="jp-carousel-comment-form-url-field">
      </fieldset>
    </div>
    <input type="submit" name="submit" class="jp-carousel-comment-form-button" id="jp-carousel-comment-form-button-submit" value="Post Comment">
  </div>
</form>

POST

<form method="post">
  <input type="submit" value="Accept / Akzeptieren" class="accept">
</form>

Text Content

NEW GENERATION FINANCE – ACCOUNTING – CONTROLLING USING MICROSOFT BI STACK

 * Home
 * Learning resources
 * Speed/Performance aspects
 * About The BIccountant
 * Imprint
 * Datenschutz/Privacy


ARCHIVES

Archives Select Month August 2023  (1) March 2023  (1) February 2023  (2)
January 2023  (3) December 2022  (1) October 2022  (2) September 2022  (1) May
2022  (2) March 2022  (5) February 2022  (2) December 2021  (3) August 2021  (1)
June 2021  (1) May 2021  (1) March 2021  (1) February 2021  (1) January 2021
 (5) November 2020  (2) October 2020  (1) September 2020  (2) July 2020  (1)
June 2020  (1) May 2020  (3) April 2020  (3) March 2020  (2) February 2020  (2)
January 2020  (1) December 2019  (1) November 2019  (1) October 2019  (2)
September 2019  (2) July 2019  (3) May 2019  (3) April 2019  (6) March 2019  (3)
February 2019  (2) January 2019  (3) December 2018  (1) November 2018  (2)
October 2018  (3) September 2018  (1) August 2018  (4) July 2018  (3) June 2018
 (2) May 2018  (3) April 2018  (4) March 2018  (1) February 2018  (2) January
2018  (4) December 2017  (1) November 2017  (1) October 2017  (3) September 2017
 (4) August 2017  (6) July 2017  (4) June 2017  (6) May 2017  (5) April 2017
 (1) March 2017  (3) February 2017  (2) January 2017  (6) December 2016  (4)
November 2016  (3) October 2016  (1) July 2016  (1) June 2016  (1) May 2016  (2)
April 2016  (4) March 2016  (3) February 2016  (3) January 2016  (1) December
2015  (2) November 2015  (1) October 2015  (4) September 2015  (3) August 2015
 (9)


THE BICCOUNTANT

New Generation Finance,  Accounting & Controlling using Microsoft BI stack




BUSINESS INQUIRIES

info@thebiccountant.com


LOG IN

Email
Password
Remember Me
Log In


CATEGORIES

 * Accounting (24)
 * Allgemein (17)
 * Bug warning (3)
 * Charting (8)
 * Cube (7)
 * Data Modelling (11)
 * Dataflow (3)
 * Dataflows (5)
 * DAX (23)
 * Excel (89)
 * Finance (6)
 * Flow (6)
 * Function (23)
 * little Tipps&Tricks (41)
 * M (118)
 * nerdy (9)
 * Power Automate (4)
 * Power BI (156)
 * Power Pivot (29)
 * Power Query (158)
 * Python (1)
 * R (9)
 * SQL (1)
 * SSAS (6)
 * Tools (7)


SEARCH BY TAG

Accounting Charting Cube DAX Excel Finance Flow Function JSON M Performance
PowerBI Power BI Power Pivot Power Query PowerQuery R SSAS Text transformation
Tools
 * Menu
 * Sidebar


POWER BI CLEANER

Edit: I am retiring support for this tool for now, as you can find an
alternative here: Measure Killer | Brunner BI.
Please feel free to use this tool as it is, but I am not fixing any bugs or
provide further help for it. Power BI Cleaner is now published with ‘Power BI
Sidetools’ by Didier
Terrien: https://github.com/didierterrien/Power-BI-Sidetools/releases Didier may
provide help in some cases. Please post issues here: 
https://github.com/didierterrien/Power-BI-Sidetools/issues

In this post I’m sharing a new version of my Power BI Cleaner tool. Besides from
some bug fixes it can now automatically identify and analyze reports that are
connected to an Analysis Services Model in tabular mode.

Edit: Currently it is not available to analyze “thin reports” in the service du
to missing authentication feature. The generation of the token via the Power BI
Rest API sites is temporarily out of service currently and will be restored
soon.

Just a warning that the live connections to Analysis Service or shared Power BI
dataset might be slow, as Power Query will retrieve the data from all datasets
that are available for your from the service. I might include a feature to
pre-select datasets in a future release if there is interest. So please add
comments if you want to see this feature.

If you are new to the tool: It shows you which DAX expressions are used where in
your reports and also indicates which elements can be deleted or hidden. It
covers most use cases from visual fields, titles or format properties over
filters on all levels, calculation groups, roles, incremental refresh policies…

As before, there are 2 versions: A basic Power BI version and an Excel-version,
that adds some very convenient additional features: The option to analyze thin
reports (connected to Analysis Services or a shared Power BI dataset) and to
generate scripts that delete unused measures and hides unused columns
automatically.


BASIC POWER BI CLEANER VERSION

FILL IN PARAMETERS

First you have to open the Power BI file whose data model you want to analyse.
Then open the Cleaner Power BI template file and fill in the local port number
and file path of the file to analyze into the 2 mandatory parameters.

Enter parameters for the new Power BI Cleaner file

I’m always using DAX Studio to grab the port number, but there are other methods
described in this article as well. Please use only the numeric part (not
“localhost”):

Retrieve port number from DAX Studio

When you try to refresh the data, you will be prompted with a dialogue about how
to connect to your local Power BI model:

Connect to data model that runs on a local Analysis Services instance

Just accept the default dialogue (“Use my current credentials”).

Next comes a prompt for Privacy Levels. Click on “Ignore Privacy Levels” in
there:

Ignore Privacy levels in this prompt

If this prompt is not appearing and privacy levels are still enabled, you might
get an error message (ending: “…. Please rebuild this data combination”). You
then have to ignore them via the menu: File -> Options and settings -> Options
-> Privacy (under: CURRENT FILE) -> Ignore the Privacy Levels..:

Ignore the Privacy Levels through the menu in the Power BI Cleaner

 

Also, depending on your settings in the global options, you might be prompted
with a couple of warning screens about native database queries:

Native Query Warnings

This is to inform you that you’re executing some custom code against a database.
This database is the local instance of SQL Analysis Services that is the basis
for Power BI Desktop and automatically running in the background. These warning
screens will pop up for all the 15 DMV queries unfortunately and you can only
disable the warnings in the global settings of Power BI. Which is something I
usually don’t recommend for security reasons, as you can run scripts against a
DB that alters or deletes records. So this could open doors for malicious code
or simply screw things up if you don’t know what you’re doing. So one should
handle this very carefully.

Also, if you’re running the pbit for the first time, the refresh might not
succeed at the first time and you might see these symbols at the top of the
screen:

Apply changes again if opening the pbit for the first time.

Simply hit “Apply changes” again and the results will finally show up. I
recommend to save the new pbix as your personal sample and reuse that to avoid
having to refresh the file twice in the future.


MAIN REPORT

Power BI Cleaner main report

OVERVIEW PAGE

On the Overview page, you see a matrix with table and column names in the row
section and usage information in the columns. Upon readers requests I’m
distinguishing between fields that are not used at all in the report (1) and
those who are used in DAX expressions, but the fields it has been used in, have
not been used in the report at the end (2). So fields in here can also be
deleted from the model. But depending on when you delete them, you might break
some measures or calculated column temporarily. But those are the ones that can
also be deleted at the end. The decision to delete a column in the source or
hide it in the report might depend on the column size, which is also given at
the end of the matrix (5).

In the next column you see where the used fields are actually used (3). So these
fields should stay in the model. The next column is just a summary, indicating
if the field can be deleted or not.

To the left there are some filters that you might find useful. For example, you
can select all fields that can be deleted by choosing the first box in the “Can
be deleted”-filter (6).

If you expand the fields in the row section, you will first see the DAX
expression of the field. After that, you see where this field has been used
throughout the report.

DAX EXPRESSIONS PAGE

The last page “DAX Expressions” simply holds a table with all DAX expressions in
the model as a reference of to quickly check items. You can also use the search
box to find strings in DAX expressions.


DELETE OR HIDE UNUSED ITEMS AUTOMATICALLY: EXCEL VERSION

The Excel-version can also generate scripts for you that can delete unused
measures or hide unused columns automatically. It also allows to analyze thin
reports: Meaning Power BI reports that don’t have an own data model but instead
use a live connection to a central data model in the service or use a live
connection to an Analysis Services Tabular model (on prem or Azure).

I strongly recommend to close all other Excel files when working with this
solution, especially when you’re analyzing thin reports (Analysis Services or
PBI Live Connection). It will occupy resources and Excel might also become
unresponsive for some time.

For these queries to run, you have to disable the privacy settings on
file-level:

Disable privacy levels in Excel

Then you have to select the modus:



SINGLE POWER BI DESKTOP FILE

If you choose “PBI_Desktop_pbix” the following fields will show up:



In cell B8 you have to enter the port number just like in the PBI version. In
B37 you must fill in the path to the pbix like in the Power BI version or the
path to a pbit version. Both will work.

Click on “Refresh main result” will refresh the data and move you over to the
“Result” page. This looks just the same than in the Power BI version.

The next page “Measures_Delete” holds a table to the left with one row for each
measure that is not used. Based on it, the table to the right will generate a
code that can be used to delete all the measures using the Tabular Editor tool.

Delete unused Measues

Before using this functionality, I strongly recommend to make a copy of your
Power BI report, as the following actions are not officially supported by
Microsoft. So if they break your report, they will not help you out on it.

The left table contains a list of detected measures that are not used anywhere
in the report. If you want to keep some of the unused measures from there,
simply delete the “Delete”-entry in column “Action” and it will be excluded from
the code. Once you’re happy with the selection, click the button “Create and
copy code”. This will refresh the table to the right that  contains the code
that would automatically delete all the listed measures if used in Tabular
Editor.

USE TABULAR EDITOR TO DELETE OR HIDE UNUSED ITEMS

Then you can simply copy the code, open Tabular Editor (LinkToProVersion,
LinkToFreeVersion) and paste the code into the “Advanced Scripting” window.
Click run, save the file and the cleanup is done:

Open Tabular Editor

Connect to PBIX model

Select default settings

Paste code from Excel and run script

Save modifications to the model

In your PBIX-model, the measures should be gone now.

The same principle goes for the columns, that can be hidden with the tables from
sheet “Columns_Hide”.


ANALYZE THIN REPORTS

BASED ON FILES

Also in the Excel-version, there is a table where you can add all the files to
analyze that are using the currently opened dataset. Yes: You can analyze “thin
reports” with it that don’t have an own data model, but are connected to a
centrally published model instead. Simply add the path to the pbix or
pbit-versions into table “ReportFile (s)” from cell B37 onwards on the
“Instructions”-sheet. The model that holds the central dataset must be open in
Power BI Desktop and its port number entered into B11 of the “Instructions”
page.

In the pivot table on the “Result”-sheet, the last column level field is “Report
File(s)” and there you can see in which (thin) report each element is used.

DIRECTLY FROM THE SERVICE

And to make it even more convenient, you can also analyze your reports from the
Power BI service directly. This requires an app registration that provides the
access to this data. This article provides a guide on how to register such an
app. The app needs to have “Workspace.ReadAll” and “Report.ReadAll” permissions.
To authenticate with this service from Excel, you can create and grab a token
from one of the API endpoint-pages, like: Datasets – Get Datasets – REST API
(Power BI Power BI REST APIs) | Microsoft Docs. There you must click on “Try
it”:

Connect to endpoint to grab token

Next the authentication will start and you have to login to the account that has
access to the app. On success, you’ll see a window like so that displays the
token. Click on “Copy”:

Copy token from webpage

On the “Instructions” page of the xlsx choose modus “PBI_Live_Connection”. Paste
the copied authentication figures into row 30/31:



Also fill in the port number into B25 and the dataset ID into B26. You find the
dataset ID in the URL if you edit the dataset details in the service like so for
example:

Grab shared dataset ID from URL

2 DIFFERENT CONNECTION MODES

If you have admin rights in Power BI, the queries can fetch details from all
workspaces in your tenant. Therefore you have to switch the value in cell B27 to
“As Admin”. Otherwise, keep the default entry “As User”. In that case, only the
details of those reports will be displayed, where you have access to.

Please note, that even measures that have been created in the thin reports
(report level measures) will be detected as well. Just have in mind, that they
will not be covered by the automatic

As long as there is an entry for the DatasetID in cell B26, Excel will try to
use the dataset and the corresponding token. After a while, the token will
become invalid. To generate a new one, simply refresh the page where you’ve
grabbed it from. If you haven’t already, now it’s time to vote to enable the
usage of custom connectors in Excel so this cumbersome process can be omitted:
Add support for custom data connectors (Power Query M extensions) – Welcome to
Excel’s Suggestion Box! (uservoice.com)


REPORTS CONNECTED TO AN ANALYSIS SERVICES MODEL

If you select modus “Analysis_Server” the following dialogue will appear:



Enter the path to the SQL server into B15, or “localhost” if it’s running you
your local machine. The database name goes into B16 and the credential in
B19/B20 follow the same mechanics than described above under “Directly from the
service”.

Optionally, you can add links to local files if they should be analyzed as well.

Just have in mind, that these methods can take quite a while, because in order
to identify all reports in the Power BI service that use the specified datasets,
Power Query has to retrieve metadata from all datasets and all data sources that
live in your tenant (and you have access to).


AREAS COVERED

The following areas of field usage are covered in these versions:

 * DAX expressions (measures, columns, hierarchies, tables, report level
   measures, roles, bins, calculation items and title expressions)
 * Incremental refresh policies
 * Relationships
 * Filters (visual-, page- and report level)
 * Visuals (incl. conditional formatting)

 


LIMITATIONS

 * When upgrading your workspace, your reports might be rebound to the dataset.
   In that case, the API will not return any data.
 * Automatic retrieval by the API will also not work for reports in the old
   workspace format.
 * Fields used in connected Excel reports are not covered by this solution.
 * Detection will not work when sensitivity label are applied.
 * Fields in custom visuals might not be properly detected. If you find problems
   there, please report.
 * Certain special characters in measures will lead to false negatives (meaning
   they will be shows and unused/can be deleted). So far, I could only identify
   the triangle (∆), but I cannot rule out that other characters can also be
   affected. The following special signs seem to work alright: |, (, ), \, “.
   What you can do instead, is to rename a measure with the triangle sign, once
   used in the visual. This will be picked up correctly.


WARNING

I cannot guarantee that this all works 100% correct, so you’re using it on your
own risk. Don’t forget to make a copy of your file before starting to throw out
your measures and columns. If you find any bugs, please mention them in the
comments.


FEATURE REQUESTS

Are there additional functionalities that you’d like to see in this tool?
Post it in the comments, please.


POTENTIAL PROBLEMS

RUN-TIME ERROR ‘1004’

This error message might indicate the your token isn’t valid any more and you
have to refresh it:



… PLEASE REBUILD THE DATA COMBINATION – ERROR

If you’re getting a warning that data sources cannot be combined, you must
disable privacy settings like so:

Disable privacy levels in Excel

 

If you experience further problems with the Power BI Cleaner tool, please post
them in the comments or send me an email and I will do my best to get them
sorted.

Current version: 2022-Jan-8: V7 (bugfixes)


DOWNLOAD FILES

PBIT: Power BI Version Cleaner Gen2

Excel version with macros: Cleaner Gen2 Excel-Version with Macros

Enjoy and stay queryious


 * © 2023
 * Powered by WordPress
 * Theme: Tatami by Elmastudio

Top


FOLLOW ME!

 * Twitter
 * YouTube
 * GitHub
 * LinkedIn
 * RSS Feed

 

Loading Comments...

 

Write a Comment...
Email (Required) Name (Required) Website


Privacy & Cookies: This site uses cookies. By continuing to use this website,
you agree to their use.
To find out more, including how to control cookies, see here: Privacy Policy /
Datenschutzerklärung