How to read data from a JSON File into Power BI Data Model

How to Load data from a JSON File into Power BI Data Model

Scenario:
Suppose we have a JSON File with sample data as per below, which we want to load into the Power BI Data Model.

JSON Data:

--------------------------------------------------------------------------------------------------------

{

  "data": [

    {

      "Order_ID": "1001",

      "Order_Date": "2025-01-01",

      "Product_Segment": "Electronics",

      "Product_Name": "Laptop",

      "Country": "USA",

      "Sales": 1500.00

    },

    {

      "Order_ID": "1002",

      "Order_Date": "2025-01-02",

      "Product_Segment": "Furniture",

      "Product_Name": "Office Chair",

      "Country": "Canada",

      "Sales": 300.00

    },

    {

      "Order_ID": "1003",

      "Order_Date": "2025-01-03",

      "Product_Segment": "Electronics",

      "Product_Name": "Smartphone",

      "Country": "Germany",

      "Sales": 800.00

    },

    {

      "Order_ID": "1004",

      "Order_Date": "2025-01-04",

      "Product_Segment": "Appliances",

      "Product_Name": "Refrigerator",

      "Country": "India",

      "Sales": 1200.00

    },

    {

      "Order_ID": "1005",

      "Order_Date": "2025-01-05",

      "Product_Segment": "Apparel",

      "Product_Name": "T-shirt",

      "Country": "Australia",

      "Sales": 50.00

    },

    {

      "Order_ID": "1006",

      "Order_Date": "2025-01-06",

      "Product_Segment": "Electronics",

      "Product_Name": "Tablet",

      "Country": "UK",

      "Sales": 600.00

    },

    {

      "Order_ID": "1007",

      "Order_Date": "2025-01-07",

      "Product_Segment": "Furniture",

      "Product_Name": "Desk",

      "Country": "France",

      "Sales": 400.00

    },

    {

      "Order_ID": "1008",

      "Order_Date": "2025-01-08",

      "Product_Segment": "Appliances",

      "Product_Name": "Washing Machine",

      "Country": "Japan",

      "Sales": 900.00

    },

    {

      "Order_ID": "1009",

      "Order_Date": "2025-01-09",

      "Product_Segment": "Apparel",

      "Product_Name": "Jacket",

      "Country": "Brazil",

      "Sales": 120.00

    },

    {

      "Order_ID": "1010",

      "Order_Date": "2025-01-10",

      "Product_Segment": "Electronics",

      "Product_Name": "Smartwatch",

      "Country": "Taiwan",

      "Sales": 200.00

    }

  ]

}

--------------------------------------------------------------------------------------------------------

We can use the default Power BI JSON Connector to load the data from the JSON File.

The Power Query steps in involved in loading the JSON dataset is as follows:

let

    Source = Json.Document(File.Contents("C:\Users\Tamatam\Desktop\MyLearn\Power BI\Inputs\ds_Sample_Orders.json")),

    Convert2Table = Table.FromRecords({Source}),

    ExpandListColumns = Table.ExpandListColumn(Convert2Table, "data"),

    ExpandColumnRecords = Table.ExpandRecordColumn(ExpandListColumns, "data", {"Order_ID", "Order_Date", "Product_Segment", "Product_Name", "Country", "Sales"}, {"Order_ID", "Order_Date", "Product_Segment", "Product_Name", "Country", "Sales"}),

    ChangeType = Table.TransformColumnTypes(ExpandColumnRecords,{{"Order_ID", Int64.Type}, {"Order_Date", type date}, {"Product_Segment", type text}, {"Product_Name", type text}, {"Country", type text}, {"Sales", Int64.Type}})

in

    ChangeType

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to create a Summary of Products with No Sales using DAX in Power BI

How to use SUMMARIZECOLUMNS Function to create a Summary of Products with No Sales in Power BI DAX

Scenario:
Suppose we have a Data Model as follows:

The relationships are as follows:

In this Data Model, we may know that some of the Products does not have the sales in some specific Months.

Sales = Sum(fct_Orders_Sample[Net_Sales])

Now we want to the generate the Summary of Products with No Sales. We can achieve this Scenario using DAX as follows:

Prod_No_Sales_Summary =

VAR _YearFilter =

    FILTER( VALUES(dim_Calendar[DimYear]),

        dim_Calendar[DimYear] IN {2020})

VAR _MonthFilter =

    FILTER(VALUES(dim_Calendar[MonthName]),

        dim_Calendar[MonthName] IN {"Jan","Feb", "Mar"})

VAR _ProdFilter =

    FILTER( VALUES(dim_Products[Product_ID]),

        NOT(ISBLANK(dim_Products[Product_ID])))

VAR _Summary =

FILTER(

    SUMMARIZECOLUMNS(

        dim_Calendar[DimYear],

        dim_Calendar[MonthName],

        dim_Products[Product_ID],

        dim_Products[Product_Name],

        _YearFilter,         _MonthFilter,

        _ProdFilter,       

        /* "NoSales", ISBLANK([Sales]) */

        "NoSales", ISBLANK(COUNTROWS(fct_Orders_Sample))

      ),

    [NoSales]=TRUE()

)

RETURN

_Summary

Result:

Please not that I have filtered the result only for specific months.

---------------------------------------------------------------------------

We can also achieve the above result using following version of DAX:

VAR _YearFilter =

    FILTER(

        VALUES(dim_Calendar[DimYear]),

        dim_Calendar[DimYear] IN {2020}

    )

VAR _ProdFilter =

    FILTER(

        VALUES(dim_Products[Product_ID]),

        NOT(ISBLANK(dim_Products[Product_ID]))

    )

VAR _Summary =

    CALCULATETABLE(

        SUMMARIZECOLUMNS(

            dim_Calendar[DimYear],

            dim_Calendar[MonthName],

            dim_Products[Product_ID],

            dim_Products[Product_Name],

            _YearFilter,

            _ProdFilter,

            "NoSales", IF(ISBLANK(COUNTROWS(fct_Orders_Sample)), TRUE(), FALSE())

        ),

        KEEPFILTERS (dim_Calendar[MonthName] IN {"Jan", "Feb", "Mar"})

    )

RETURN

    FILTER (_Summary, [NoSales] = TRUE())

Result:

---------------------------------------------------------------------------

We can also create a summary of Products with and without Sales as per below:

Prod_Sales_Summary =

VAR _YearFilter =

    FILTER( VALUES(dim_Calendar[DimYear]),

        dim_Calendar[DimYear] IN {2020})

VAR _MonthFilter =

    FILTER(VALUES(dim_Calendar[MonthName]),

        dim_Calendar[MonthName] IN {"Jan","Feb", "Mar"})

VAR _ProdFilter =

    FILTER( VALUES(dim_Products[Product_ID]),

        NOT(ISBLANK(dim_Products[Product_ID])))

VAR _Summary =

CALCULATETABLE(

    ADDMISSINGITEMS(

     /* Select Columns used in the Summary */       

        dim_Calendar[DimYear],

        dim_Calendar[MonthName],

        dim_Products[Product_ID],

        dim_Products[Product_Name],

        SUMMARIZECOLUMNS(

            dim_Calendar[DimYear],

            dim_Calendar[MonthName],

            dim_Products[Product_ID],

            dim_Products[Product_Name],

            _YearFilter,

            _MonthFilter,

            _ProdFilter,  

            "NetSales", [Sales],

            "NoSalesFlag", ISBLANK(COUNTROWS(fct_Orders_Sample))*1

            ),

    
        /* Group by Columns used in the Select */   

        dim_Calendar[DimYear],

        dim_Calendar[MonthName],

        dim_Products[Product_ID],

        dim_Products[Product_Name]

        ),

    KEEPFILTERS(_YearFilter),

    KEEPFILTERS(_MonthFilter),

    KEEPFILTERS(_ProdFilter)

    )

RETURN

_Summary

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to use Encode and Decode methods for binary-to-text conversions in Power Query

How to Encode and Decode the binary-to-text conversions using M-Query in Power BI
In Power Query, Base64 Encoding is a standard method for encoding binary data into Base64 encoded text format using ASCII characters.

1) Encoding Text:
Base64 Encoding is a way to represent binary data (like images or files) in a text format using only ASCII characters. It's called Base64 because it uses 64 different characters to represent the data.

Please Note: 

This method should not be used as a secure method for protecting sensitive information. It is simply a way to represent binary data in an ASCII string format. Anyone who can decode Base64 can easily get the original data.

How Base64 Encoding Works:
a) Convert to Binary: First, the text is converted to binary data.
b) Split into Groups: The binary data is split into groups of 6 bits.
c) Map to Characters: Each group is mapped to one of the 64 characters in the Base64 alphabet (A-Z, a-z, 0-9, +, and /).

Example:

let

    text2Encode = "Sample_1#X2$y#3&Z_9@a8#B&7C_Key",

    binaryData = Text.ToBinary(text2Encode),

    base64Encoded = Binary.ToText(binaryData, BinaryEncoding.Base64)

in

    base64Encoded

Result:
U2FtcGxlXzEjWDIkeSMzJlpfOUBhOCNCJjdDX0tleQ==

2) Decoding Text:
Base64 Decoding is the reverse process of Base64 encoding. It converts the Base64 encoded text back into its original binary form(like an image, file, or text).

How Base64 Decoding Works:
a) Map to Binary: Each character in the Base64 encoded text is mapped back to its 6-bit binary representation.
b) Combine Groups: The binary groups are combined into the original binary data.
c) Convert to Text: Finally, the binary data is converted back to the original text or file.

Example:

let

    base64Text = "U2FtcGxlXzEjWDIkeSMzJlpfOUBhOCNCJjdDX0tleQ==",

    binaryData = Binary.FromText(base64Text, BinaryEncoding.Base64),

    decodedText = Text.FromBinary(binaryData)

in

    decodedText

Result:

Sample_1#X2$y#3&Z_9@a8#B&7C_Key

#--------------------------------------------------------------Thanks--------------------------------------------------------------#