Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |