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 ,

Solution looks promising and its works with data provided.

However data which i have provided is dummy one and in actual data it is not necessary that we always have two rows between every heads, for e..g data could be any thing like as below

 

ColA        ColB         ColC
Data         Data         Data
L-1             2              3
L-2             5              6
L-3            22            3322
L-4            231          233
Cloud       Cloud      Cloud
L-3             8               9
L-4            11             12
L-6            78             121
Azure       Azure        Azure
L-5            14             15
L-6            17              18

 

- It is not necessary that we have only three heads such as Data, Cloud, Azure etc, it could be any number.

- Number of columns are not fixed are also not fixed, it could be ColA, ColB, ColC, ColD etc, again it could be any number.

- Number of rows between two heads can be any, it not neccessarily two, in above data set, there are 4 rows for Data, 3 rows for Cloud etc.

 

Please suggest!!

Thanks

Amit

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey amsrivas ,

 

then you have to find a way to split them and do the separation in portions.

If you have an unlimited number of columns you should change it to a way that is not fixed on specific columns.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
  • 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