Product:
Microsoft Power BI service Dataflow

Issue:

How change on-prem gateway from prod to dev?

Solution:

You have to click on EDIT for the dataflow.

Then click on EDIT TABLE.

Then on the HOME ribbon find OPTIONS.

Scroll down to Data Load, and select the on-prem gateway you should use in this dataflow.

To showcase how to change the gateway in a dataflow project, this article uses a query that connects to a local folder as an example.

This query previously used a gateway named “Gateway A” to connect to the folder. But “Gateway A” no longer has access to the folder due to new company policies. A new gateway named “Gateway B” is registered and now has access to the folder that the query requires. The goal is to change the gateway used in this dataflow project so it uses the new “Gateway B.”

To change the gateway:

1. From the Home tab in Power Query, select Options.

   

2. In the Options dialog box, select Data load, and then select the gateway to use for your project, in this case, Gateway B.

   

    Tip

   If there were recent changes to your gateways, select the small refresh icon to the right of the drop-down menu to update the list of available gateways.

3. After selecting the correct gateway for the project, in this case, Gateway B, select OK to go back to the Power Query editor.

To change the dataflow to use a different SQL view for the table of data, go to the navigation icon in the right list, and select a different view from the SQL database, from the list of view in the databases.

More Information:

https://learn.microsoft.com/en-us/power-query/change-gateway-dataflow 

Product:

Microsoft SQL Azure

Issue:

What user have the db_owner role?

Solution:

Start SSMS and login to your SQL azure database, and run below script (found on internet)

SELECT CASE princ.[type]
WHEN 'S' THEN
princ.[name]
END AS [UserName],
CASE princ.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
princ.[name] AS [DatabaseUserName],
NULL AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--database user
sys.database_principals AS princ
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
WHERE princ.[type] IN ( 'S', 'U' )
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT CASE memberprinc.[type]
WHEN 'S' THEN
memberprinc.[name]
END AS [UserName],
CASE memberprinc.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
memberprinc.[name] AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Role/member associations
sys.database_role_members AS members
JOIN
--Roles
sys.database_principals AS roleprinc
ON roleprinc.[principal_id] = members.role_principal_id
JOIN
--Role members (database users)
sys.database_principals AS memberprinc
ON memberprinc.[principal_id] = members.member_principal_id
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT '{All Users}' AS [UserName],
'{All Users}' AS [UserType],
'{All Users}' AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc], 
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Roles
sys.database_principals AS roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
JOIN
--All objects 
sys.objects AS obj
ON obj.[object_id] = perm.major_id
WHERE
--Only roles
roleprinc.[type] = 'R'
AND
--Only public role
roleprinc.[name] = 'public'
AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY princ.[name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.permission_name,
perm.state_desc,
obj.type_desc; --perm.[class_desc]

More information:

https://www.scarydba.com/ 

Product:
Microsoft Power BI portal / service

Issue:

How move a dataflow gen1 from one workspace to a another workspace?

Solution:

From source workspace, click on the 3 dots … and select export json file.

Save the file on your hard disk.

Change the powerbi workspace to the new workspace where you want to import the dataflow

Click on “new item” and select Dataflow Gen1

Click on “import model”

Select the json file you have saved – and click open.

When the dataflow is imported, you need to update the credentials to connect to the data source.

Click on the 3 dots … and select edit.

Click on the edit table icon for one of the tables in the list.

You may get a message like: Credentials are required to connect to the SQL source.

Click on options.

If you use a power bi query gateway to access your SQL database, you need to select it at data load, and click OK.

Click Save and close.

Now it should work to update (refresh) your dataflow.

If it does not help, you may need to go to manage connections, and try to set a different user to access the data source.

More Information:

https://forum.enterprisedna.co/t/moving-data-flows-and-report-to-a-new-workspace/22166 

Move dataflows across workspaces with the Power BI REST API

https://www.phdata.io/blog/migrating-queries-from-power-bi-desktop-to-dataflows/ 

Product:
Microsoft PowerBI desktop

Issue:

A column in a table is not sorted correct, it sorted alphabetical and not by date. Even do the column should show a date format.

Solution:

Inside PowerBI Desktop, go to the table view.

Select the column (that you want change the sort order for) and click on icon for “sort by column”.

In the dropdown menu, select the column who have a correct format, in our example the date column.

Now check the table in Report view.

You need to repeat this for all columns, in your report in PowerBI for Desktop.

More Information:

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

https://www.techrepublic.com/article/how-to-sort-by-column-power-bi/ 

https://help.zebrabi.com/kb/power-bi/add-a-sorting-column/ 

Product:

Microsoft Power BI desktop
Microsoft Windows

Issue:

Error when you try to add a measure in the visualizations list of data.

Solution:

Try to add a new column instead. The icons tell what type of value it is.

More Information:

Another important difference between measures and calculated columns is that measures are evaluated in the filter context of the visual in which they are applied. The filter context is defined by the filters applied in the report such as row selection, column selection, report filters and slicers applied. Measures are only evaluated at the level of granularity they are plotted at. As calculated columns are computed when you first define them/ when you refresh your dataset, they do not have access to the filter context. Calculated columns are calculated outside of the filter context and do not depend on user interaction in the report.

When you write a calculated column, you need to rely only on the row context. The row context, is simply the notion of a current row. It specifies which row we are calculating the values for, so that DAX can look at other values in the same row. In a calculated column, the row context is implied. When you write an expression in a calculated column, the expression is evaluated for each row of the table. The calculated column has knowledge of the current row.

By contrast, measures implicitly do not have a row context. This is because, by default, they work at the aggregate level. So, you cannot refer to columns directly in a DAX measure, you will get an error because no row context exists. This is because the measure will not know which row to choose in the table

https://endjin.com/blog/2022/04/measures-vs-calculated-columns-in-dax

Measures vs. Calculated Columns in Power BI

https://www.thedataschool.com.au/mipadmin/the-differences-between-new-measure-and-new-column-in-power-bi/ 

https://biinsight.com/define-measure-table-power-bi-desktop/ 

https://exceleratorbi.com.au/conditional-formatting-using-icons-in-power-bi/

Product:
Planning Analytics 2.0.9.19
Microsoft Windows 2019 server
Google Chrome

Issue:
The background image in TM1WEB is changed on the server – but the change is not shown on end users web browsers.

Solution:

On many browsers, pressing Control-F5 will reload the page and bypass the cache. The Wikipedia “Bypass your cache” page has details for all modern web browsers.

Or you can change the name of the file PA_header.svg in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\images\login to something else. Then you need to update all code that calls that file – can be this css-html files for tm1web:

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\carbon.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\all.css

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\LoginDialog.css

Or clean the Web Browser cache at the end users laptop.

More Information:

https://medium.com/@gulshan_n/overcoming-persistent-image-caching-how-to-ensure-visitors-see-your-latest-website-changes-cd89a1434e1a 

https://www.avast.com/c-how-to-clear-cache

Resurrecting images from a web browser cache can be a useful way to recover lost or deleted images that you previously viewed online. Here’s a step-by-step guide on how to do this for various web browsers:

Google Chrome

1. Access Cache:
   – Type chrome://cache in the address bar and press Enter. This will take you to a list of cached files.
2. Find Images:
   – You can’t view the images directly, but you can see the URLs. To find images, you can also use a tool like ChromeCacheView (a third-party utility) that allows you to view and extract images from the cache more easily.
3. Extract Images:
   – If using ChromeCacheView, download and run it. It will display cached files, including images. You can select the images you want to save and extract them to your computer.

Mozilla Firefox

1. Access Cache:
   – Type about:cache in the address bar and press Enter. This will show you the disk and memory cache information.
2. Find Images:
   – Look for the section that lists the cache entries. You can find images by checking the file type and URL.
3. Extract Images:
   – You can use Mozilla’s cache viewer or a third-party tool like Firefox Cache Viewer to extract images more conveniently.

Microsoft Edge

1. Access Cache:
   – Type edge://cache in the address bar and press Enter. This will show you cached items.
2. Find Images:
   – Similar to Chrome, you might need a third-party tool like EdgeCacheView to find and extract images.

Safari

1. Access Cache:
   – For Safari, the cache is not easily accessible like in other browsers. You can use Terminal commands or third-party tools.
2. Find Images:
   – You can also look in the ~/Library/Caches/com.apple.Safari/ directory to find cached files.
3. Extract Images:
   – Use a third-party application like SafariCache to help retrieve images from the cache.

General Tips

• Look for File Types: When browsing the cache, look specifically for file types like .jpg, .png, or .gif.
• Use Third-Party Tools: Tools like WebCacheImage, ChromeCacheView, and others can simplify the process.
• Browser Extensions: Some extensions can help manage and view cached files directly from the browser.

https://www.linkedin.com/pulse/11-ways-fix-google-chrome-loading-images-benjamin-ladegbaye 

How to refresh cached images and files in Chrome

Product:
Microsoft SQL server

Issue:

In a view of dates, we only want the 5 last years and the 5 future years, from today date. How do it?

Solution:

Create a view of the table listed above, where you define it like below:

CREATE VIEW [DM].[DMDateView]
AS
SELECT [key_dimDate]
,[DateIndex]
,[Date]
,[Day]
,[DaySuffix]
,[Weekday]
,[WeekDayName]
,[DOWInMonth]
,[DayOfYear]
,[WeekOfMonth]
,[WeekOfYear]
,[ISOWeekOfYear]
,[Month]
,[MonthName]
,[Quarter]
,[QuarterName]
,[Year]
,[MMYYYY]
,[MonthYear]
,[FirstDayOfMonth]
,[LastDayOfMonth]
,[FirstDayOfQuarter]
,[LastDayOfQuarter]
,[FirstDayOfYear]
,[LastDayOfYear]
,[FirstDayOfNextMonth]
,[FirstDayOfNextYear]
FROM [DM].[dimDate]
where 1=1
and [Date] BETWEEN DateAdd(yy, -5, GetDate()) AND DateAdd(yy, +5,GetDate())

More Information:

https://www.mickpatterson.com.au/blog/add-auto-increment-to-a-column-in-sql-server

https://www.w3schools.com/sql/func_sqlserver_dateadd.asp

https://www.dbvis.com/thetable/sql-server-dateadd-the-complete-guide/

Product:

Microsoft SQL server

Issue:

How check if a column can be converted to numeric from string?

Solution:

Start SSMS and enter below SQL code to list rows that is not convertable to numbers:

 select TOP (1000) * FROM [schemaname].[tablename]
where ISNUMERIC (columname) = 0

Below code to make a full copy of a table:

SELECT *
INTO newtable 
FROM oldtable
WHERE condition;

Below code to only list rows where the column content is above 15 charcters:

 select TOP (1000) * FROM [schemaname].[tablename]
where LEN (columnname) >= 15

More Information:

https://www.w3schools.com/sql/sql_select_into.asp

https://www.airops.com/sql-guide/how-to-check-length-string-sql

https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16 

Product:

Microsoft SQL server

Issue:

How update a value in a column in a existing table – for example divide with 1000?

Solution:

UPDATE table_name
SET value1 = value1 / 1000
WHERE condition ;

Keep in mind to check the key dim table, so you include the correct rows in the update.

  The key value for the version need to be collected from the version table.

The account value you want to update in fact table may have more than one key value, that you need to check and include.

In the fact table you can use the update command, but you need to carefully test the where clause so you update the correct rows.

Use below code to update the column value where the account is the defined value

 UPDATE [DM].[fact]
SET value = value / 1000
WHERE 1=1
and key_dimversion = 3
and ( key_dimTabellkonto = 55180 or 
key_dimTabellkonto = 61453 or 
key_dimTabellkonto = 62504 or 
key_dimTabellkonto = 66683 or 
key_dimTabellkonto = 67730 or 
key_dimTabellkonto = 69823 )

Use below code to get a sum of column value:

 SELECT
Sum(value)
FROM [DM].[fact]
WHERE 1=1
and key_dimversion = 3
and ( key_dimTabellkonto = 55180 or 
key_dimTabellkonto = 61453 or 
key_dimTabellkonto = 62504 or 
key_dimTabellkonto = 66683 or 
key_dimTabellkonto = 67730 or 
key_dimTabellkonto = 69823 )

This can be written easier with a SQL JOIN between the tables.

UPDATE a
SET a.value= a.value / 1000
FROM [DM].[fact] a
INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto]
INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion]
WHERE 1=1
and c.[version] = 'budget'
and b.[account] = '95000' ;

To see the sum

SELECT SUM (a.value)
FROM [DM].[fact] a
INNER JOIN [DM].[dimTabellkonto] b ON a.[key_dimTabellkonto] = b.[key_dimTabellkonto] 
INNER JOIN [DM].[dimVersion] c ON a.[key_dimVersion] = c.[key_dimVersion]
WHERE 1=1
and c.[version] = 'budget'
and b.[account] = '95000' ;

More Information:

https://www.w3schools.com/sql/sql_update.asp 

https://learnsql.com/blog/sql-division-operator/

https://www.w3schools.com/sql/sql_join.asp

https://www.sqlshack.com/learn-sql-join-multiple-tables/ 

https://www.geeksforgeeks.org/sql-update-with-join/ 

https://www.datacamp.com/tutorial/sql-update-with-join

https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/ 

https://www.javatpoint.com/sql-update-with-join

Product:

Microsoft Excel

Issue:

You have a list of values (maybe dimensions) in column A and you want to find if they exist in column C. How do it in Excel?

Solution:

Mark column C, and enter the name “searcharea” in the top left name area (called the name box) , press enter. Now you have created a “range”.

Go to cell D1.

Enter Formula = COUNTIF ( searcharea; A1 )

Mark cell D1, and with the mouse drag down all the way to the last cell.

The formula should update automatic, if you do right, to in cell D2 be = COUNTIF ( searcharea; A2 )

This should give a 1 in the rows where the value in cell A exist in the C column.

More Information:

https://www.wps.com/academy/check-if-value-is-in-list-in-excel-(3-easy-methods)-quick-tutorials-1865235/ 

https://www.exceltip.com/lookup-formulas/check-if-value-is-in-list-in-excel.html

https://excel-tutorial.com/why-excel-formulas-use-comma-or-semi-colon/ 

Force Excel to use commas (,) instead of semicolons (;) in separating formulas

https://exceljet.net/glossary/list-separator 

https://exceljet.net/glossary/named-range