Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
Im using Listed Columns to get data without specifying column names and numbers.
My data comes every week by new excel files respectively and the thing is sometimes column names or numbers or order could change quite often.
e..g not real just imagine worst case
W1
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
W2
ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF ColumnG
W3
ColumnA ColumnC ColumnD ColumnE ColumnF
W4
ColumnC ColumnB ColumnD ColumnE ColumnF ColumnA
W5
ColumnC ColumnZ ColumnD ColumnE ColumnF ColumnA
I cannot stack all the file data since columns are changing its order and name.
Can we unpivot them before Expand data table as below from each files?
if all the values are unpivotted at the beginning then it will be no issue with column number/order changes.
But as I mentioned, every week new files come and should be cumalative, I cannot command this query every week.
Week | Category | Value |
1 | ColumnA | 00 |
1 | ColumnB | 00 |
1 | ColumnC | |
(skipped) | 00 | |
5 | ColumnC | 00 |
5 | ColumnZ | 00 |
5 | ColumnD | 00 |
This is the sample of my data that staking all files but number/name of column header are changing.
Only 2 Category cases(5G and LTE) shown in snapshot but more than 30 types of it so I cannot split data table for each category neither. although I split them, unexpectable column name/number changes would happen in the future.
I think only way is unpivotting this data to the format as above table and then stacking all files.
Any suggestion pls?
Solved! Go to Solution.
Hi @jeongkim ,
I understand the request you have however I believe I'm not being very clear on how Power Query can solve the problem.
When you get data from a file you first must open that file and then make the several steps you need to make the changes, this means that you need to open each file and proceed to the pivot of the tables and then unpivot based on all the names you will get.
This can be achieve automating the process of accessing each file using a custom function that will allow you to return the data inside each file in the format you refer:
Week | Category | Value |
1 | ColumnA | 00 |
1 | ColumnB | 00 |
1 | ColumnC | |
(skipped) | 00 | |
5 | ColumnC | 00 |
5 | ColumnZ | 00 |
5 | ColumnD | 00 |
After this you can continue with additional steps.
The custom function may allow you to do a more simpler or complex treatment of the data based on if you want have many steps replicated or just some simple changes and then the complex ones to be applied to the full set of files after the first interaction.
If you allow me the question once again what is the type of file you are dealing with belive it's not reference on your initial post and I can send you an example of what I mean.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @jeongkim ,
Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.
Hi @jeongkim ,
We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.
Thank You.
Hi @jeongkim ,
@MFelix , has provided a good solution that aligns with your requirements. Do let us know if you need any additional information or further assistance. Have you been able to resolve this issue?
If yes, kindly mark the helpful response as the accepted solution and give it a 'Kudos' to assist other community members in finding it more easily.
it's not that I asked, my qeury is not asking how to do unpivot.
Hi @jeongkim ,
I understand the request you have however I believe I'm not being very clear on how Power Query can solve the problem.
When you get data from a file you first must open that file and then make the several steps you need to make the changes, this means that you need to open each file and proceed to the pivot of the tables and then unpivot based on all the names you will get.
This can be achieve automating the process of accessing each file using a custom function that will allow you to return the data inside each file in the format you refer:
Week | Category | Value |
1 | ColumnA | 00 |
1 | ColumnB | 00 |
1 | ColumnC | |
(skipped) | 00 | |
5 | ColumnC | 00 |
5 | ColumnZ | 00 |
5 | ColumnD | 00 |
After this you can continue with additional steps.
The custom function may allow you to do a more simpler or complex treatment of the data based on if you want have many steps replicated or just some simple changes and then the complex ones to be applied to the full set of files after the first interaction.
If you allow me the question once again what is the type of file you are dealing with belive it's not reference on your initial post and I can send you an example of what I mean.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @jeongkim ,
If you use the unpivot without defining the column names you are able to get what you need something similar to this:
= Table.UnpivotOtherColumns(Previous_Step_Name, {}, "Attribute", "Value")
Not sure what is the type of file you are refering a XLSX or a CSV but depending on it you may need some additional steps.
What you can do is a Files From folder and then make the transformation using the Function using the previou code has the last step.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMy query is how to unpivot data in backend before expanding data table.
Hi @jeongkim ,
If you don't get inside the file you cannot unpivot your data since Power Query needs to have access to the data before doing transformations to it.
Using a custom function you can do it for each file without impacting all the other and then get the data unpivot as a final result.
What type of files are you working with? (CSV?, Excel?) I can do a example file for you to check if it works for you.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsmy purpose is not opening the files manually for automation linking to power automate.
Thanks though, it seems there is no solution for my query.
HI @jeongkim ,
I apologize if I did not make myself clear when I refer to open each file is not manually you can use Power Query to make the unpivot of the data before appending them all.
What you need to create is a custom function in the power query that does the unpivot for the file.
I ask you again do you want a mockup of this type of setup?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português