Forum Discussion
Data formatting and arrangement
Hi
I am new to power BI and i am trying to automate a particular output and simultaneouly trying to visualise insights from my data. I am successful in creating the visuals but stuck in creating the below output.
Input data format
Below is a dummy of a my data set (input) and the format will remain consistant. Column 'Selection' will be added in the input file, wherein i will be selecting some of the entries (highlighted as A, B, C and D). The second row (Office) will remain consistant and is important to my input.
| Selection | First Name | Last Name | Department | Salary | Source |
| Office | ABC | Corp | NA | ||
| James | Butt | Accounting | $44,000 | Internal | |
| A | Josephine | Darakjy | Payroll | $42,000 | Internal |
| Art | Venere | Communication | $49,000 | Internal | |
| B | Lenna | Paprocki | Research | $58,000 | Internal |
| Donette | Foller | Finance | $62,000 | Internal | |
| C | Simona | Morasca | Real Estate | $75,000 | External |
| Mitsue | Tollner | Goodwill | $55,000 | External | |
| D | Leota | Dilliard | Marketing | $60,000 | External |
| Sage | Wieser | Customer Service | $40,000 | External |
and see below the kind out i desire in a table format so that it can be exported from Power BI.
In the below output, i am unable to automate the "minimum, maximum and average rows" (the order of min, max and average should be the same) and the "source row". These will be dynamic and if there is any change in the data, it will automatically update.
| Selection | First Name | Last Name | Department | Salary |
| A | Josephine | Darakjy | Payroll | $42,000 |
| B | Lenna | Paprocki | Research | $58,000 |
| C | Simona | Morasca | Real Estate | $75,000 |
| D | Leota | Dilliard | Marketing | $60,000 |
| Minimum | $42,000 | |||
| Maximum | $75,000 | |||
| Average | $58,750 | |||
| Office | ABC | Corp | ||
| Source: Internal and External |
PLease confirm if there is a way to do it.
3 Replies
- Greg_Deckler
Community Champion
Anonymous - I'm honestly not sure what you are going for here. Are you trying to do this via Power Query or are you trying to create a table visualization? Power BI is not generally used to transform one ugly, awfully formatted set of data into an even uglyier, even more awful set of data spit out as a text file?? You may have the wrong tool if that is what you are going for. I can't figure out what is up with the blank rows, etc. quite honestly. You are using different columns for different things. That is a trainwreck. Probably be better off using Perl to text parse it from what I can tell or take whatever software is generating it or needs it for input out into field somewhere and beat it with a baseball bat ala Office Space until it commit die.
With all that said, there actually is a way to do it. I attached the PBIX below sig. Table (14) and Table 5.
Table 5 = VAR __Office = MAXX(FILTER('Table (14)',[Selection]="Office"),[Selection]) VAR __First = MAXX(FILTER('Table (14)',[Selection]="Office"),[First Name]) VAR __Last = MAXX(FILTER('Table (14)',[Selection]="Office"),[Last Name]) RETURN SELECTCOLUMNS( UNION( FILTER('Table (14)','Table (14)'[Selection]<>"Office"), { ( "","","","Minimum",MIN('Table (14)'[Salary]),CONCATENATEX('Table (14)',[Source]," and ")), ( "","","","Maximum",MAX('Table (14)'[Salary]),CONCATENATEX('Table (14)',[Source]," and ")), ( "","","","Average",AVERAGE('Table (14)'[Salary]),CONCATENATEX('Table (14)',[Source]," and ")), ( __Office,__First,__Last,"","","" ), ( "Source: " & CONCATENATEX(DISTINCT('Table (14)'[Source]),[Source]," and "),"","","","","") } ), "Selection",[Selection], "First Name",[First Name], "Last Name",[Last Name], "Department",[Department], "Salary",[Salary] )I feel like I need to go take a shower after providing this solution... 😛
- Greg_Deckler
Anonymous - Any verdict?
