Forum Discussion
Helper IData model from flat SCV file in Power BI Desktop
Hi guys,
I'm still fairly new to Power BI and I run into a problem that I cannot solve by myself. Also not after doing a lot of research on the internet.
It concerns this situation: I created an SCV export from an energy system with multiple divices, that looks like the next example data. The devices have simular signals, but the signals are in different columns:
CSV export data
| Date | Temperature - Device 1 | Wind - Device 1 | Temperature - Device 2 | Wind - Device 2 |
| 01-01-20 | 16,5 | 14,2 | 16,4 | 16,9 |
| 02-01-20 | 15,2 | 15,8 | 15,3 | 15,1 |
| 03-01-20 | 13,1 | 12,2 | 13,5 | 12,8 |
My end goal is to create a table/overview with the device and wind per date, something like this:
| Date | Device | Wind |
| 01-01-20 | Device 1 | 14,2 |
| 01-01-20 | Device 2 | 16,9 |
| 02-02-20 | Device 1 | 15,8 |
| 02-02-20 | Device 2 | 15,1 |
To be able to do this, I already made a copy of the table, split it into 2 different tables and change the column names:
Device 1
| Date | Temperature | Wind |
| 01-01-20 | 16,5 | 14,2 |
| 02-01-20 | 15,2 | 15,8 |
| 03-01-20 | 13,1 | 12,2 |
Device 2
| Date | Temperature | Wind |
| 01-01-20 | 16,4 | 16,9 |
| 02-01-20 | 15,3 | 15,1 |
| 03-01-20 | 13,5 | 12,8 |
Also I created this 'Devices' table by hand
Devices
| DeviceId | Title |
| 1 | Device 1 |
| 2 | Device 2 |
If I'm not wrong, the final table should look something like this, where the deviceId is connected to the devices table:
| Date | DeviceId | Temperature | Wind |
| 01-01-20 | 1 | 16,5 | 14,2 |
| 01-01-20 | 2 | 16,4 | 16,9 |
| 02-01-20 | 1 | 15,2 | 15,8 |
| 02-01-20 | 2 | 15,3 | 15,1 |
| 03-01-20 | 1 | 13,1 | 12,2 |
| 03-01-20 | 2 | 13,5 | 12,8 |
Is this correct? And how can I achieve this in Power BI Desktop?
Any help is greatly appreciated! Many thanks in advance.
Josef , Unpivot should help
Hi Josef ,
You can follow these steps in the power query editor:
1.Select the date column and unpivot other columns
2.Split the column by "-"
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.V-lianl-msft & amitchandak many thanks for your reply! I'm tried the unpivod option, but this didn't get the desired result for me.
I not sure if it is the most efficient way, but I did the following what solved the issue for me:
I copied the table two times. In the first copy I only kept the columns of device 1 and in the second only for device 2. Then 1 added a custom column 'deviceId' to both tables, for device 1 I put '1' in the custom column and for device two '2'.
Then I merge the two tables with Compbine > Append Queries as New, to create a new table where all the device data has the same columns, and the data from each device is distinguishable from each other by the deviceId.
For me the issue is now solved. Again many thanks for your replies!
5 Replies
- amitchandak
Super User
Josef , Unpivot should help
V-lianl-msftCommunity Support
Hi Josef ,
You can follow these steps in the power query editor:
1.Select the date column and unpivot other columns
2.Split the column by "-"
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JosefV-lianl-msft & amitchandak many thanks for your reply! I'm tried the unpivod option, but this didn't get the desired result for me.
I not sure if it is the most efficient way, but I did the following what solved the issue for me:
I copied the table two times. In the first copy I only kept the columns of device 1 and in the second only for device 2. Then 1 added a custom column 'deviceId' to both tables, for device 1 I put '1' in the custom column and for device two '2'.
Then I merge the two tables with Compbine > Append Queries as New, to create a new table where all the device data has the same columns, and the data from each device is distinguishable from each other by the deviceId.
For me the issue is now solved. Again many thanks for your replies!
- V-lianl-msft
Hi Josef ,
It's glad that you have solved your problem.
Please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.