cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LLORVIN
Regular Visitor

Reorganized datas from a flat cube

Hi all, 

 

I need your help.

LLORVIN_0-1634836093895.png

I'd like to reorganized datas from this table with M.

I want to create a fonction that will read "Line Items" and create a list with all distinct values.

Then, from the list, create new tables, one per item in the list and associate all datas from the fields "Time", "Attribut" and "Valeur".

To be perfectly clear, at the end, I would have:

A table named "Réel Ytd" with fields "Time", "Attribut" and "Valeur",

Another table named "Réel" with fields "Time", "Attribut" and "Valeur",

...

 

I can use the actual table as it is, but I'll have to use a lot of mesures. It's not a real problem, a bit longer to design the dashboard. But working from many tables with less mesures would be easier to understand and to maintain the dashboard, for my customer. 

 

Maybe I could obtain the same result with an other way, datas come from Anaplan, in fact, datas come from a cube in .csv format.

 

Thanks by advance for your help,

 

Ludo

 

1 ACCEPTED SOLUTION

AFAIK There is no way to do it. Power Query certianly cannot. You might be able to script it on Tabular Editor, but that would require you to enable unsupported functions, and any issues (even if possible) will not be supported by MS. They don't support T/E editing M and creating objects.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

We need data, and a good mockup of expected results. I'm not typing in all of that data in the image.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
LLORVIN
Regular Visitor

OK, sorry, that was my first post.

 

The goal is to transform the "Line items" column into tables containing the others fields (following screenshots)

The source :

LLORVIN_0-1634843209242.png

 

What I need:

The first disctinct value (in the black rectangle) becomes the name of a new table containing datas of the red rectangle.

Then next distinct value, until the end.

With the datas below, I should have a bit less than 20 tables at the end.

 

LLORVIN_1-1634843541572.png

 

The result with the first value of Line Items Column:

LLORVIN_2-1634843703157.png

 

 To complete, here are some datas.

VersionsLine ItemsTimeACTIVITE COMMERCIALE- Détails- Bloc
ValeurRéel ytd01/11/2021000
ValeurRéel ytd01/12/2021000
ValeurRéel01/03/20210321145,354442374,06655
ValeurRéel01/04/20210321145,354442374,06655
ValeurB2101/01/2021039248,432043866,712
ValeurB2101/02/2021089130,8161417441,8281
ValeurDernier estimé01/01/2021018044,41178182,41425
ValeurDernier estimé01/02/2021050809,4313268,70908
ValeurEcart B2101/01/20210-21604,42026-3684,29775
ValeurEcart B2101/02/20210-39518,38484-17173,11902
Valeur% B2101/01/20210-0,550453079-0,952824454
Valeur% B2101/02/20210-0,443375104-0,984593984
ValeurEcart dernier estimé01/01/20210-400,40
ValeurEcart dernier estimé01/02/20210-11970
Valeur% estimé01/01/20210-0,0221896950
Valeur% estimé01/02/20210-0,0235586180
ValeurRAF B2101/01/20210-281896,9223-38507,35455
ValeurRAF B2101/02/20210-232014,5382-24932,23845
Valeur% RAF B2101/01/202108,18237411410,95868183
Valeur% RAF B2101/02/202103,6030788042,42945099
ValeurRAF dernier estimé01/01/20210-303100,9426-42191,6523
ValeurRAF dernier estimé01/02/20210-270335,9231-42105,35747
Valeur% dernier estimé01/01/2021017,79749644232,2958132
Valeur% dernier estimé01/02/202106,320585493157,694956
ValeurRéalisé mensuel01/01/2021017644,01178182,41425
ValeurRéalisé mensuel01/02/2021031968,4195286,29483
ValeurRéalisé N-1 mensuel01/01/2021049664,091641666,05275
ValeurRéalisé N-1 mensuel01/02/2021037142,06924-482,03122
ValeurRéalisé N-1 ytd01/01/2021049664,091641666,05275
ValeurRéalisé N-1 ytd01/02/2021086806,160881184,02153
ValeurRéalisé N-1 ytd01/12/20210357295,191487124,43588
ValeurEcart avec reel ytd b2101/01/20210-21604,42026-3684,29775
 
 
 
 
 
 
 

 

I think it's better to find the solution now.

 

Thanks to all.

 

Ludo

Are you wanting Power Query to create those tables? It cannot. Power Query cannot create other objectes (lists, tables, values, etc) - it can only manipulate the data it has. It can convert the existing data into a table, list, or whatever, but it cannot create other objects on that side pane.

If that isn't what you mean, please advise.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
LLORVIN
Regular Visitor

Hi Edhans, 

 

I want new tables based on the source "Run Export Action", maybe another way exists to do it, but I want to do it with Power BI.

In fact I want to split "Run Export Action" to obtain many tables to resume, it doesn't matter how, but with Power BI.

 

Regards,

 

Ludo

 

 

AFAIK There is no way to do it. Power Query certianly cannot. You might be able to script it on Tabular Editor, but that would require you to enable unsupported functions, and any issues (even if possible) will not be supported by MS. They don't support T/E editing M and creating objects.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

LLORVIN
Regular Visitor

Too bad.

 

Thx Edhans

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors