Forum Discussion
Reorganized datas from a flat cube
Hi all,
I need your help.
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
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.
6 Replies
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.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 :
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.
The result with the first value of Line Items Column:
To complete, here are some datas.
VersionsLine ItemsTimeACTIVITE COMMERCIALE- Détails- Bloc Valeur Réel ytd 01/11/2021 0 0 0 Valeur Réel ytd 01/12/2021 0 0 0 Valeur Réel 01/03/2021 0 321145,3544 42374,06655 Valeur Réel 01/04/2021 0 321145,3544 42374,06655 Valeur B21 01/01/2021 0 39248,43204 3866,712 Valeur B21 01/02/2021 0 89130,81614 17441,8281 Valeur Dernier estimé 01/01/2021 0 18044,41178 182,41425 Valeur Dernier estimé 01/02/2021 0 50809,4313 268,70908 Valeur Ecart B21 01/01/2021 0 -21604,42026 -3684,29775 Valeur Ecart B21 01/02/2021 0 -39518,38484 -17173,11902 Valeur % B21 01/01/2021 0 -0,550453079 -0,952824454 Valeur % B21 01/02/2021 0 -0,443375104 -0,984593984 Valeur Ecart dernier estimé 01/01/2021 0 -400,4 0 Valeur Ecart dernier estimé 01/02/2021 0 -1197 0 Valeur % estimé 01/01/2021 0 -0,022189695 0 Valeur % estimé 01/02/2021 0 -0,023558618 0 Valeur RAF B21 01/01/2021 0 -281896,9223 -38507,35455 Valeur RAF B21 01/02/2021 0 -232014,5382 -24932,23845 Valeur % RAF B21 01/01/2021 0 8,182374114 10,95868183 Valeur % RAF B21 01/02/2021 0 3,603078804 2,42945099 Valeur RAF dernier estimé 01/01/2021 0 -303100,9426 -42191,6523 Valeur RAF dernier estimé 01/02/2021 0 -270335,9231 -42105,35747 Valeur % dernier estimé 01/01/2021 0 17,79749644 232,2958132 Valeur % dernier estimé 01/02/2021 0 6,320585493 157,694956 Valeur Réalisé mensuel 01/01/2021 0 17644,01178 182,41425 Valeur Réalisé mensuel 01/02/2021 0 31968,41952 86,29483 Valeur Réalisé N-1 mensuel 01/01/2021 0 49664,09164 1666,05275 Valeur Réalisé N-1 mensuel 01/02/2021 0 37142,06924 -482,03122 Valeur Réalisé N-1 ytd 01/01/2021 0 49664,09164 1666,05275 Valeur Réalisé N-1 ytd 01/02/2021 0 86806,16088 1184,02153 Valeur Réalisé N-1 ytd 01/12/2021 0 357295,1914 87124,43588 Valeur Ecart avec reel ytd b21 01/01/2021 0 -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.