Production demand matrix automatic refresh

[Anonymous]

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

[amitchandak]

@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

[Anonymous]

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,

[TomMartens]

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 

[Anonymous]

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,

[TomMartens]

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 

[Anonymous]

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,

[TomMartens · Accepted 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]

Worked like magic !! I have been trying to figure this out on my own for quite a few days.

Thanks a lot!!

[TomMartens]

Hey @Anonymous ,

I'm happy it helped!

Regards,
Tom