Getting data from different columns and multiple rows into a single row (and filtering off some)

[Anonymous]

Hello,

I've been looking through the forum and despite I've found very good ideas, all of them ended in unsucessful results.

I've got the table structure below. Note that Value column sometimes contains same or similar values as text...

MainID	Field	Text	DateTime	Number	Value
1	Name	John			John
1	Country	US
1	Date		01.01.2018
1	Sales			15000
1	Product	Xenon1			Xenon 1
2	Name	Mark
2	Country	UK
2	Date		01.04.2018
2	Sales			2000
2	Product	Xenon 1			Xenon 1
2	Comments	On vacation
3	Name	Andy			Andrew
3	Country	Canada
3	Date		01.03.2018
3	Sales			3000
3	Product	Xenon2			Xenon 2

And obviously I want to get all in a single row, like this:

MainID	Name	Country	Date	Sales	Product	Comments
1	John	US	01.01.2018	15000	Xenon1	null
2	Mark	UK	01.04.2018	2000	Xenon1	On Vacation
3	Andrew	Canada	01.03.2018	3000	Xenon 2	null

I've managed to put every value in just one column (converting datetime and number to text. No problem, will change them later) by using a modification of:

= Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")

 I did one run for value, datetime and number, and then another one for text. This got me quite close to what I want,

MainID	Label	Value
1	Name	John
1	Country	US
1	Country	1
1	Date	01.01.2018
1	Sales	1500
1	Product	Xenon1
2	Name	Mark
2	Country	UK
2	Date	01.04.2018
2	Sales	2000
2	Product	Xenon 1
2	Comments	On vacation
3	Name	Andy
3	Country	Canada
3	Date	01.03.2018
3	Sales	3000
3	Product	Xenon2

But still... The 'Label' rows are duplicated (I just forced the example with 'Country') what produces errors when pivoting the columns. I just want to keep the values that begin with a number, let's say '1'.

Sorry for the long post, but I wanted to detailed the issue as much as possible. I'll really appreciate any help. Thanks!

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

As we can see from the sample data, every attribute only has one value except the "Name". The workaround or solution could be as follows. You can check out the demo here.

1. Add a new column with the formula below.

if not ([Value] is null) 
    then [Value]  
else if not ([Text] is null) 
    then [Text] 
else if not ([DateTime] is null) 
    then [DateTime] 
else if not ([Number] is null) 
    then [Number] 
else "CHECK AGAIN"

2. Keep columns "MainID", "Field", "Custom" only and delete other columns.

3. Povit the columns "Field" and "Custom".

Best Regards,

Dale