John Liu Blog

Every drop counts

Microsoft Fabric References

Some reference articles about Microsoft Fabrics. Moving Dataflows Gen 2 to different workspaces

Posted by John Liu Tuesday, August 13, 2024

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