John Liu Blog

Every drop counts

Power BI Dynamic Parameter in Direct Query

In Power BI, you may want to pass the filter value user selected as parameter to your direct query data source to SQL Server. You can achieve this using dynamic M parameter. Please note, Bind to parameter option will NOT be available in Power BI unless a Direct Query query presence. In Power Query Editor, create a M parameter using Manage Parameters. The data type for the parameter needs to match with the type of data you try to bind with.

Posted by John Liu Wednesday, August 7, 2024

Power BI M functions

Empty table This query returns a table with a single text column called YourColumnName and no rows. let Source = #table( type table [YourColumnName = text], {} ) in Source Conditional Formatting Highlight max and min This function returns the color value for the Total Sales figure based on if the value is the max/min or other. MaxMin = VAR Sales = [Total Sales] VAR MaxSalesOverall = MAXX( ALLSELECTED('dimDate'[Year],'dimDate'[Quarter],'dimDate'[QuarterNo]), CALCULATE([Total Sales]) ) VAR MinSalesOverall = MINX( ALLSELECTED('dimDate'[Year],'dimDate'[Quarter],'dimDate'[QuarterNo]), CALCULATE([Total Sales]) ) VAR Result = SWITCH( TRUE, Sales = MaxSalesOverall, "#22957e", Sales = MinSalesOverall, "#ff908c", "#7bc8fe" ) RETURN Result Above Average This function indicates if the Total Sales value is above average (1) or not (0).

Posted by John Liu Tuesday, August 6, 2024

Power Automate Remove Elements from JSON Array

In Power Automate, when processing JSON array, you might need to remove unwanted elements from the JSON array. When you read an Excel worksheet from OneDrive or SharePoint, the result is a JSON array and two internal elements (@odata.etag and ItemInternalId) will be added into the result JSON array. To remove the @odata.etag and ItemInternalId from the result array, add a Select data operation action. Set the From value to be expression:

Posted by John Liu Monday, July 29, 2024

Excel Tips

Date/Datetime When using Power Automate to extract Excel data into CSV, the date value might be converted to a numeric value. That numeric value is the date difference between 30/DEC/1899 and the actual date value. If the numeric value is a integer, that represent a date (or time portion all 0s). If the numeric value is a float/decimal, that represent a datetime value. So, if you need to convert that integer value back to it’s original date value in SQL:

Posted by John Liu Saturday, July 27, 2024

SQL Server Replication Setup

To setup SQL Server transactional replication, the publisher needs to be Standard or Enterprise edition. Subscriber can be any edition (except SQL Server Compact), but will require standard or Enterprise edition as well if configure merge (bi-directional) replication. We will need to create some local Windows accounts to run replication agents: Agent Location Account name Snapshot Agent Publisher <server name>\repl_snapshot Log Reader Agnet Publisher <server name>\repl_logreader Distribution Agnet Publisher/subscriber <server name>\repl_distribution Merge Agnet Publisher/subscriber <server name>\repl_merge On Publisher machine, create a file share for the snapshot folder.

Posted by John Liu Thursday, July 25, 2024