Data formatting and arrangement

[Anonymous]

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.

[Greg_Deckler]

@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... 😛

[Anonymous]

Thanks Greg.

I understand it might not be a right tool for this but as mentioned earlier, it is only part of the work. Most of the visuals are complete and it was a small portion which cannot be moved out of PBI and has to be adjusted.

Thanks for the assist, I will try the soultion.

[Greg_Deckler]

@Anonymous - Any verdict?