Forum Discussion

moragbeedie78's avatar
moragbeedie78
Regular Visitor
7 years ago
Solved

Linking Table

Struggling to figure this one out, but im sure there is a simple way im not seeing! Im trying to link a large data set from a survey of various things.   On one part of the dataset im asking to ra...
Need Help
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi moragbeedie78

    I make a test with your example dataset.

    1. in Edit Queries

    (1)select "Service1 a-Usefulness " to "Service1 e-Website and online resources" five columns, then select "Unpivot columns" (under "Transform" tab)

     

    (2) split columns 

    click on column [Attribute], select split columns by delimeter

     

    click on column [Attribute.2], select split columns by delimeter

     

    then rename columns

    [Attribute.1]->[service]

    [Attribute.2.2]->[service-sub]

     

    close&apply

     

    2. create a measure

    Measure = COUNT(Sheet6[Value])

    add columns and measure in a matrix visual

    3. add columns and measures in the column chart as below, you will see

     

    Best Regards

    Maggie

moragbeedie78's avatar
moragbeedie78
Regular Visitor
7 years ago

Large dataset (a section of it)

 

Countif Table in another tab of excel (Just an example - i know the amounts dont equate to the table above!):

 

And the result I want in Power Bi is:

 

The dataset is absolutlely massive as this was a large survey - this section is only one small part of it. If i can filter by area (Campus type on this visual) then i'll be able to pull this into the rest of the service results/graphs (100+ of them). 

 

Any guidance would be greatly appreciated!

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

In the Query Editor, select the first 3 columns and select "Unpivot other columns".  Now build your visual.