transformation of data arranged in columns

[jeanfra]

Hello to everyone,

First, I would like to apologize for my English which is not my mother tongue.  

I have to analyses an excel dataset of materials flows structured like this “simple” example :

To build	Resource	Unity	From	To	01/2025	02/2025	03/2025	04/2025	05/2025	06/2025	…
Work 1	Concrete	m3	outside	Work 1	0	0	23	0	0	0
Work 1	Steel Beam	T	outside	Work 1	11	3	5	0	0	0
Work 2	Bricks	T	outside	Work 2	0	23	3	0	2	0
Work 3	Wires	ml	outside	Work 3	0	0	0	5	0	4
Work 4	Bricks	ml	outside	Work 4	0	75	77	0	0	0
Work 4	Wires	ml	outside	Work 4	0	1	2	35	4	0
Work 4	wastes	m3	Work 4	outside	0	0	0	35	41	0

As you can imagine my dataset if formed of hundreds of thousands of lines and thousands of columns.

I’m these days starting to work with power bi, so the solution can be very simple, but I really don’t see it.

I would like to represent my flows by the time for different works or resources or whatever.

I already tried to separate the date named columns in a second pivoted table, but I still can’t represent what I want to.

Thank you for the help you will provide ! 

Regards, 

François 

[Musadev]

Hi @jeanfra 

That's great news. Filter out the errors, there may data or datatype issue. Let me know if you need any help to mark it a complete solution for your post. 

[Musadev · Accepted solution]

Hi @jeanfra 

Your data is in pivoted format and you can unpivot the data with few steps. First check the output of the sample data. 

Steps to follow:
1- load and transform the data into Power Query Editor

2- if want to keep the zeros then change it's format to text, else keep it number. (in number the 0 value will be discarded.

3- select all the columns other than dates

4- in the Transform tab, go to unpivot Columns and select the unpivot other columns

after the above steps, you will get the data as shown in the screenshot. 

If this helps your Post? consider it as a Solution

[jeanfra]

Hello Musadev, 

Thank you so much for your answer. 

I tried without beliving it will works... (My dataset is more than 60 000 lines and some 250 "date columns"). But it worked very fast. 

However the unpivoted column generate 4% of error (value = error). I'll check out but I think some of the headers where wrong.