Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Creating visual with data on the same row

Hi, I've allready recived alot of great help from this community in processing my data. Now it is time to start visualizing it so that it get somewhat more userfriendly.    I've already done quite ...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous

 

hi, Anonymous

First you should learn about UNION Function and SELECTCOLUMNS Function in dax.

and Unpivot function in data.

https://support.office.com/en-us/article/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

Second try these two formulas

 

Table 2 = 

    UNION(
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week1],"Bevegelige uke",[Bevegelige uke 1],"Fastsittende uke",[Fastsittende uke 1]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week2],"Bevegelige uke",[Bevegelige uke 2],"Fastsittende uke",[Fastsittende uke 2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week3],"Bevegelige uke",[Bevegelige uke 3],"Fastsittende uke",[Fastsittende uke 3])
 
    )
Table 3 = 

    UNION(
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week1]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Mature femals adult week3]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Bevegelige uke 1]),
       SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Bevegelige uke 2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Bevegelige uke 3]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Fastsittende uke 1]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Fastsittende uke 2]),
        SELECTCOLUMNS('Table1',"Globalweek",[Globalweek],"Mature femals adult week",[Fastsittende uke 3])

    )

 

Best Regards,

Lin

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Lin and thank you for taking the time to reply. 

 

I tried your code, but got the same issue as I did myself " The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" 

See pic 1 and 2 - as you can see i only did one small change exchanging Globalweek with a uniqe site treatment ID. But both scenarios produced the same result. 

 

Sadly i don't think i can share the full .pbix as it now contains some sensitive data. However, I have included some screenshots of all the categories that i need to un pivot in order to visualize propperly. 




All of this information is captured on a single row, and is calculated with calculated columns outside of the power query editor, so it is not an option to use.
Basicly the spreadsheet covers what happens when a farmer uses a medical product and follows different aspects in the following weeks - All public data. 

As a uniqe identifier i think the [Yearly site treatment ID] is fitting. 
The goal here is to get somehting like this
Company - Treatment ID - Lice stage (adult female, bevegelig, fastsittende) - week 1 - week 2 - week 3 - week 4
So that i eventually can make a line graph showing the development over time. Upon writing this i actually got an idea and tried, however i'm still getting the same issue. 

 

Any suggestions on how to get it right? 

In advance thank you very much for any help!