Data model from flat SCV file in Power BI Desktop

[Josef]

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. 

[amitchandak · Accepted solution]

@Josef , Unpivot should help

refer : https://radacad.com/pivot-and-unpivot-with-power-bi

[V-lianl-msft · Accepted solution]

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 · Accepted solution]

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.

[Josef · Accepted solution]

@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!

[V-lianl-msft]

Hello @Josef,

I'm glad you solved your problem.
Please accept the answers that make sense as a 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, please consider accepting it as the solution to help other members find it more quickly.

[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.

[amitchandak · Accepted solution]

@Josef , Unpivot should help

refer : https://radacad.com/pivot-and-unpivot-with-power-bi