Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power Query expert, 🙂
this is my Excel table i would like to analyse with power bi(but first to transform with power query). I would like to expand all table at once time(Every week a new table will be added, so next week a Week45_2020 will be created)
Here is the code related to the table, i charge a Onedrive file which is supplied by a power automate connected will my server exchange to download files from my emails. then remove some columns and remains the date and rows with datas i need to analyse.
I can do it manually but it's really annoying and not very useful if i need to go back to my file every week to expand all.
The Table Function "Table.expandTableColumn" take as argument the main table(here is headers from the previous step), the column name and the new name.
Then i would like to make this week44_2020 dynamic, like a loop which can go through a list of my first column which contain all the worksheet name. For now it's a list and the function is only accepting column text value so i didn't make it to expand all this thing the dynamic way. Or maybe with a each function, i'm not that familiar with this function.
I'm just an manufacturing engineer without so much software background and new in Power Bi and m language area so i'm not so familiar how to do this. That's why i ask help here.
Could anyone advise on how to solve this problem ?
I already look for it for 4days without any results. I also checked these website and tried to adapt their code without success.
https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
https://bielite.com/blog/dynamically-expand-all-columns/
(The second one is making a giant loop of many gigabite until my computer crash)
Thank you very much in advance.
Best regards
Rémy
Hi @Remy320 ,
Why do you want to get something like below?
Index | W46 - A | W46 - B | W46 - C | W45 - A | W45 - B | W45 - C |
1 | A1 | B1 | 4 | A1 | B1 | 1 |
2 | A2 | B2 | 5 | A2 | B2 | 2 |
3 | A3 | B3 | 6 | A3 | B3 | 3 |
It is not suggested to transform data like so. Power BI, or DAX, is calculated in units of columns. So, transform data like below is better:
Name | Index | A | B | C |
W45 | 1 | A1 | B1 | 1 |
W45 | 2 | A2 | B2 | 2 |
W45 | 3 | A3 | B3 | 3 |
W46 | 1 | A1 | B1 | 4 |
W46 | 2 | A2 | B2 | 5 |
W46 | 3 | A3 | B3 | 6 |
W47 | 1 | A1 | B1 | 7 |
W47 | 2 | A2 | B2 | 8 |
W47 | 3 | A3 | B3 | 9 |
Then, you can filter what you want.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @Remy320
You need to combine this data in one table, or you need the data of your worksheets in separate columns?
In you first scenario you could read from your Excel-file (without accessing a sheet), filter your sheets as needed, transform the column "data" to promote headers (not needed if you have tables instead of sheets) and then to use a Table.Combine on your "data"-column. Here an example how it could look like
let
Source = Excel.Workbook(File.Contents("xxxx"), null, true),
Transform = Table.TransformColumns
(
Source,
{
{
"Data",
each Table.PromoteHeaders(_)
}
}
),
Combine = Table.Combine(Transform[Data])
in
Combine
in your second scenario you could add a new column, where you make the transformation, to rename the columns
let
Source = Excel.Workbook(File.Contents("xxx"), null, true),
Transform = Table.TransformColumns
(
Source,
{
{
"Data",
each Table.PromoteHeaders(_)
}
}
),
TableAdd= Table.AddColumn
(
Transform,
"newData",
each Table.RenameColumns(_[Data], List.Zip({Table.ColumnNames(_[Data]), List.Transform(Table.ColumnNames(_[Data]), (item)=> _[Name]&" - "&item )}))
),
Combine = Table.Combine(TableAdd[newData])
in
Combine
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello Jimmy801, Thanks for taking times to help me. I need to combine them to only one table with 43 rows (Not more)
Like here (I develloped a table, and i need devellop the 80 columns and keep having only 43 rows and +999 columns).this is the target i expect.
However with your 1st solution i get +999 rows
And 2nd solutions get me way too much rows (+999) and columns.
Do you have any other ideas how can i do that ?
Thank you for the support.
Best regards
Rémy
Hello @Remy320
your request was to combine all sheets in your file in one table. If you get more then 1000 rows means, that you have more then 1000 rows in all your sheets. I don't know how to get to 43 only!?
BR
Jimmy
Hi, @Jimmy801
i don't have any table with 1000 rows, they all have the same 43 rows.
All the table have the same rows (43) and between 40 and 50 columns. (Each worksheet have the same rows, and every week a new worksheet is made)
So the thing is i don't want to append datas, i want to make the same result as if i expand each table (For example if i have 100 tables, a single table with 43 rows and 100*50 columns)
Best regards
Rémy
Hello @Remy320
so your final goal is to have in power bi 100 different tables or these 100 tables not appended but connected horizontal?
BR
Jimmy
My final goal is just to connect these table horizontal (just like a append would do it vertical but i need horizontal)