Forum Discussion

amsrivas's avatar
amsrivas
Helper IV
5 years ago

Power BI Data Modelling

Hi Folks!! I have a data as below   ColA ColB ColC Data Data Data L-1 2 3 L-2 5 6 Cloud Cloud Cloud L-3 8 9 L-4 11 12 Azure Azure Azure L-5 14 15 L-...
Need Help
amsrivas's avatar
amsrivas
Helper IV
5 years ago

Hi selimovd ,

Real requirement is to load the data from the source of Azure status and create Power BI report based on that.

 

 

When i am loading into Power BI Dataser via Web Connection, i am getting data like as shown below

 

 

So here, data from Row 2 to row 13 is belongs to "Compute" and then belongs to "Developer Tools" and so on as shown in Azure status page.

 

Is there other wayout to retreive the result.

I need to have Power BI Dataset where i can filter data based on "Compute , Developer Tool etc and can see rest of the detail.

 

Please suggest, if there is any other way to achieve the same.

 

Thanks

Amit 

 

HotChilli's avatar
HotChilli
Community Champion
5 years ago

In Power Query,

Select 'Use first row as headers'.

Add a column which copies the sub-headings and puts null for the details.  You can use the interface and the code will be similar to:

#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [ColC] = "Blank" then null else if [ColC] = "Good" then null else if [ColC] = "Bad" then null else [ColC], type text),

Then select the new column and do a 'Fill Down'.

Then from the header of the most appropriate column, filter the data to only show rows of Good, blank and whatever else that is not a 'sub-heading'.

 

good luck