Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Power BI community,
I am no expert on this, and I am working on a matrix that I would like to refresh daily thru the server. I have managed to build the matrix manually, but since the header fields will be changing every day, am I afraid the table won't be able to find the new fileds. Is there anyway to assign the columns dinamically based on some sort of criteria?
Please take a the below exmple, hope I explain my self.
Thank you very much!
Customer | Part Number | Backlog | D 07/21/2020 | D 07/22/2020 | D 07/23/2020 | D 07/24/2020 | D 07/25/2020 | D 07/26/2020 | D 07/27/2020 |
Custormer XXXXX | 3657733 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Custormer XXXXX | 786326437 | 480 | 0 | 0 | 0 | 480 | 480 | 480 | 480 |
Custormer XXXXX | 653698234 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Client ZZZZZ | 432324 | 0 | 0 | 0 | 6000 | 0 | 0 | 0 | 0 |
Client ZZZZZ | 423432424 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 432 |
Client ZZZZZ | 1233 | 3456 | 0 | 0 | 0 | 0 | 3024 | 0 | 2592 |
Client ZZZZZ | 235453323 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 432 |
Client DDDDD | 321323 | 1704 | 0 | 0 | 0 | 0 | 0 | 2880 | 0 |
Client DDDDD | 425543 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 180 |
Client DDDDD | 43432555 | 408 | 0 | 1296 | 0 | 2160 | 0 | 2160 | 0 |
Client DDDDD | 534536666 | 0 | 0 | 0 | 0 | 0 | 864 | 0 | 0 |
Desired matrix
Solved! Go to Solution.
Hey @Anonymous ,
I assume the error is raised because one of the steps that are applied during the transformation is referencing the column name, that will no longer be present as time moves on.
You have to be aware of the following setting:
File --> Options and settings --> Options --> Global --> Data load --> Type Detection
I have chosen "Never detect ..."
This choice prevents that Power Query will change data types automatically after specific transformation steps. When I'm done with basic transformations I manually change the data type.
For this reason, the following screenshots of the applied steps may look a little different. The following screenshot shows all the necessary steps:
Here is the M code, taken from the advanced editor:
let
Source = Excel.Workbook(File.Contents("C:\Users\tmart\OneDrive\Desktop\DynamicDays.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Customer", "Part Number"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
As you can see, just the columns "Customer" and "Part Number" are referenced.
I assume that a step like "Change type" is responsible for raising the error as this step references columns by name. If a column is no longer available in the source file, this will raise an error.
I recommend that you remove all steps from your query, change the data type manually after you unpivoted the date columns.
Hopefully, this provides some ideas how to tackle your challenge.
Regards,
Tom
@Anonymous , If you are looking for Hybrid Table. It is not there. I have logged an idea for that. https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
Hello Amitchandak,
I am not looking for a Hybrid table, the image you see right there is just a screenshot of the matrix and the actual query that I put together to show what I was doing. What I actually need is matrix with dinamic name columns.
Sorry if that created confusion.
Thanks,
Hey @Anonymous ,
it's necessary to create a sinigle column from all the columns that will form the column headers in your matrix.
Here are some screenshots how you can achieve this.
I'm starting with a table that looks like this:
In Power Query (the query editor) I mark the columns Customer and Part Number, from the context menu I choose "Unpivot Other Columns", choosing this command from the menu is important to consider the dynamic changes of the columns:
This will lead to a new table structure that looks like this (be aware that the screenshot just contains the 2 new columns):
Don't forget to rename the new columns attribute and value.
Now you can create a report using the Matrix visual that looks like this.
Whenever the dataset will refresh the new strucure of the source data will be considered using the transformation "Unpivot Other Columns".
Hopefully, this provides what you are looking for.
Regards,
Tom
Hello Tom,
First of all, thank you so much for taking the time to read my post.
I did all the steps as you instructed and the matrix structure is exactly what I was looking for. However, same as before, I get the error "The column 'D 07/24/2020' of the table wasn't found. " once that day no longer exist on the excel spreadsheet source. Also a new day will be added to the end table everyday.
Please let me know if I did something wrong, or if there's anything else that needs to be done.
Thanks again,
Hey @Anonymous ,
can you please prepare to xlsx files that contain sample data.
Name one Day1 and the 2nd one Day2 (I'm aware that in real life the name of the will not change). Upload the files to onedrive, dropbox, or google drive and share the link.
Regards,
Tom
Hi Tom,
Here you go, I hope there's solution.
https://drive.google.com/drive/folders/1-Jtpqw1XBenFvHvvVI_t1AtZ_O3TjwSC?usp=sharing
I really need to figure this out.
Thanks,
Hey @Anonymous ,
I assume the error is raised because one of the steps that are applied during the transformation is referencing the column name, that will no longer be present as time moves on.
You have to be aware of the following setting:
File --> Options and settings --> Options --> Global --> Data load --> Type Detection
I have chosen "Never detect ..."
This choice prevents that Power Query will change data types automatically after specific transformation steps. When I'm done with basic transformations I manually change the data type.
For this reason, the following screenshots of the applied steps may look a little different. The following screenshot shows all the necessary steps:
Here is the M code, taken from the advanced editor:
let
Source = Excel.Workbook(File.Contents("C:\Users\tmart\OneDrive\Desktop\DynamicDays.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Customer", "Part Number"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
As you can see, just the columns "Customer" and "Part Number" are referenced.
I assume that a step like "Change type" is responsible for raising the error as this step references columns by name. If a column is no longer available in the source file, this will raise an error.
I recommend that you remove all steps from your query, change the data type manually after you unpivoted the date columns.
Hopefully, this provides some ideas how to tackle your challenge.
Regards,
Tom
Worked like magic !! I have been trying to figure this out on my own for quite a few days.
Thanks a lot!!
Hey @Anonymous ,
I'm happy it helped!
Regards,
Tom
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |