Data Reshaping ( multiple assignment to single assignment)

[Anonymous]

Hello! 🙂
I do have a table with (amongst other columns) the following data:

Name	Field1	Field2	Field3	Field4	Field5
abc	A1	A1	E3	S2	Q1
abc	S2	Fr	A1	U1	I1
...	...	...	...	...	...
def	A2	D3		F3	S2
def	C2	A4	F3
...	...	...	...	...	...

Notes:
There are more then two names

Field 1-5 are next to each other, "Name" is further away

Field1-5 only includes one value per cell which consists of a number and a letter

I try to get/need:

T1:

Name	Field
abc	A1
abc	A1
...
def	A2
	D3
...	...

and

T2:

Name	Field	Count
abc	A1	3
...	...	...
def	F3	2
...	...	...

Verbally spoken: I need a 1:1 assigment of name:field and also, how often a certain field occurs, regardless the Field (1-5) and the row of T1, only in dependence of the name  (so for example A1 occurs in T1 in row 1 (abc) 2x, in row 2 (abc) 1x -> abc A1 3
I only have Power BI as tool aviable and I am completly new to it. So it would be lovely, if someone could help me out! 🙂

[v-cazheng-msft · Accepted solution]

Hi, @Anonymous 

You can create a Calculated column and a Measure to get the tables you want.

Calculated column :

Name_Field =

VAR t1 =

    SELECTCOLUMNS ( 'Table', "Name", 'Table'[Name], "Field", 'Table'[Field1] )

VAR t2 =

    SELECTCOLUMNS ( 'Table', "Name", 'Table'[Name], "Field", 'Table'[Field2] )

VAR t3 =

    SELECTCOLUMNS ( 'Table', "Name", 'Table'[Name], "Field", 'Table'[Field3] )

VAR t4 =

    SELECTCOLUMNS ( 'Table', "Name", 'Table'[Name], "Field", 'Table'[Field4] )

VAR t5 =

    SELECTCOLUMNS ( 'Table', "Name", 'Table'[Name], "Field", 'Table'[Field5] )

VAR union_t =

    UNION ( t1, t2, t3, t4, t5 )

VAR tt =

    FILTER ( union_t, AND ( [Field] <> "", NOT ( ISBLANK ( [Field] ) ) ) )

RETURN

    tt

Measure :

Count = COUNTX('Name_Field','Name_Field'[Field])

The result looks like this:

Here is the pbix file.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try to unpivot the table in power query

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

Hello @amitchandak 
Thank you for your reply. The data is not pivoted from the beginning on, the base table is just a flat data structure. Do you know how I can work with your solution then?