Forum Discussion
Power BI Dynamic column quantity and Name
Morning all,
I am looking for some help on handling a table that values are constantly in flux. This dashboard is for project management, so daily to weekly, project will complete and be filtered out of my data and new ones will add. The names of the projects are also constantly changing as we progress through projects. I resolved the dynamic name issue by referencing to the column number when transforming the data vs the Column Name, but I have been unsuccessful at handling the qty of columns.
When transforming my data, the only criteria for data type is Column0 is a Date type and all other columns are Number type. Below is a shortened version but number of columns can vary from 20-30 columns week to week.
Thank you in advance,
let
Source = Excel.Workbook(Web.Contents("ABC123.xlsm"), null, true),
#"ABC123" = Source{[Item="ABC123",Kind="Sheet"]}[Data],
#"Transposed Table" = Table.Transpose(#"ABC123"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, type date},{Table.ColumnNames(#"Promoted Headers"){1}, type number},{Table.ColumnNames(#"Promoted Headers"){2}, type number},
{Table.ColumnNames(#"Promoted Headers"){3}, type number}, {Table.ColumnNames(#"Promoted Headers"){4}, type number}, {Table.ColumnNames(#"Promoted Headers"){5}, type number}})
in
#"Changed Type"
2 Replies
v-yanjiang-msftCommunity Support
Hi Anonymous ,
Seems not very clear about your problem, the applied steps you provided can't open because of the data source.
But there is a step to transpose table, so after transpose table, what problem you got. You say "have been unsuccessful at handling the qty of columns", sorry I'm not clear what's that mean, please explain more about this.
Best Regards,
Community Support Team _ kalyjHi Kalyj,
The intent of this dashboard is resource managment for labor and create a Capacity vs. Demand visual. My table I pull from has Dates in the first row, Projects down the first column and assigned number of staff assigned to each project each day. My date spread is every working day for 4 years so to make it usable in PowerBI, I transpose it, then promote the headers.
So with this table, I will Transpose it, Promote the headers as normal. Then I need to do a Change Type step. Normally when performing this step, each column would be refrenced by the name of the header, which is fine if the data headers never change.
Now the issue I am having is, when I add a project to my list or projects are completed and remove from the table, referencing the header text will break the code ( {"A", type number},). Example a week goes by and project "A" is completed and I add projects "G" and "F" my table will now look like below image. (Keeping completed projects is not realistic as my real data would contain thousands of projects, this is only an example. )
And I will get the following error as expected.
To work through the header issue by referencing the column number vs. column name. This fixes the issue that "A" is no longer the header.
The next issue I incounter is increase/decrease in projects. If my project qty increases, the added columns don't change type. If the project qty decreses, the code breaks as there is not enough columns in the data set to complete the code.
I hope this explains my situation much better and how to try and manage Dynamic names and column qty. I assume it would reference everything as a table that can grow and shrink in qty but the first column is always Date type, the rest are Number type. This is where I am stuck.