Power BI (Business Intelligence) is a Microsoft BI tool which is widely used at every level in the organization to make confident decisions, perform ETL logic and visualize data using up-to-the-minute analytics.
Here, we are targeting the issue of mixed data values coming from APIs in a single column.
Targeted audience– Developer
Use Case:,
In figure: A column containing the Records and List of records may create issues while further expanding them due to different data types.
We can expand the list and record separately, but it will duplicate columns if the data contains a similar structure to this.
JSON Sample for reference:
{
“@timestamp”: “30-11-2020 01:31:30 PM”, “postitems”:
[ {
“itemCode”: “abc1″
},
{
“itemCode”: “abc2″
},
{
“itemCode”: “abc3″,
“hours”: [{
“day”: “Monday-Thursday”,
“time”: “2:00PM – 1:00AM”
},
{
“day”: “Friday-Sunday”,
“time”: “10:00AM – 2:00AM”
}]
},
{
“itemCode”: “abc4″
} ] }
This issue can be resolved by the Power Query editor formula as given below.
let
#”source” = Json.Document(File.Contents(“d:pathfilename.json”)),
#”tabled “= Table.FromRecords({source}),
#”expandListField” = Table.ExpandListColumn(tabled, ” postitems “),
#”expandRecField” = Table.ExpandRecordColumn(expandListField, ” postitems “, {” itemCode “, “hours”}, {” itemCode “, “hours”}),
#”expandList2″ = Table.ExpandListColumn(expandRecField, “hours”),
#”fieldForRec” = Table.AddColumn(expandList2,”Rec”, each if Value.Is([hours], type record) then [hours] else null, type record),
#”fieldForList” = Table.AddColumn(fieldForRec, “List”, each if Value.Is([hours], type list) then [hours] else null, type list),
#”expandList3″ = Table.ExpandListColumn(expandRecField2, “List”)
#”Replaced Value” = Table.ReplaceValue(#”expandList3″, each [List], each if [List]=null then [Rec] else [List], Replacer.ReplaceValue, {“List”}),
#”expandRecField2″ = Table.ExpandRecordColumn(#”Replaced Value”, “Rec”, {“day”, “time”}, {“day”, “time”}),
in
expandRecField2
The formula here creates a set of lists and records into separate columns.
#”fieldForRec” = Table.AddColumn(expandList2,”Rec”, each if Value.Is([hours], type record) then [hours] else null, type record),
#”fieldForList” = Table.AddColumn(fieldForRec, “List”, each if Value.Is([hours], type list) then [hours] else null, type list),
Then expand the column with a list.
#”expandList3″ = Table.ExpandListColumn(expandRecField2, “List”)
It makes both the column with the same data type i.e., Records
Now we merge both the columns by replacing the null values in the list column.
#”Replaced Value” = Table.ReplaceValue(#”expandList3″, each [List], each if [List]=null then [Rec] else [List], Replacer.ReplaceValue, {“List”}),
Now we only have a record that contains “Employee Name” in the column, and we can expand the data to be used.
In figure: Column with an only record data type that can be further expanded and get Employee Name
Conclusion:
Here we transformed data for multiple types of values into single columns with PowerBI.
Next Steps-
Join us in a one-day workshop Dashboard in a Day (DIAD) and Paginated Reports in a Day (PRIAD) to understand and explore Power BI as a solution for business data collaboration.
With the new year upon us, this could be the right time to consider this powerful business management solution. With the flexibility to start small and scale, and with the advantages as outlined such as lower costs, unification, and ease of use, why not?
Also
Know
Why Microsoft Power BI is the leader in business analytics?
Gain advantage of a phased approach for your next BI project.
Learn
How to embed a Power BI report into an application for your customers?
Ankit Panchal
Ankit Panchal is currently working as Senior Associate in Advaiya and has worked on Microsoft technologies for past 3.5 years and has been a SharePoint expert. Currently is working on Microsoft Power Platforms.
Contact us by filling the form below for Power BI implementation and integration.
