Log In
Register
Home
New Member
July 15, 2021
Offline
Hi,
My concern is on how to have a dynamic header so that I can change the month automatically without again rewriting the query every month. Also, help me on moving the data with the change in the month (date) w.r.t. columns.I'm posting a Sample Excel file for reference. Also, I saved the file as Sample Excel.xlsb and zipped (7Z) it for upload. Pl. help me with the issue if you can. Thanks in advance.
Sample Excel : _P sheets are of primary importance. Also, when trying to establish a connection between Resource sheet and _R sheet ( in the data model) , the connection is being established from _R sheet to Resource sheet automatically which should be the other way round. Let me know on what is the issue for that?
-Regards,
Shyam
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Shyam,
When you expand columns, edit the M code:
Instead of:
= Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Employment Status#(lf)(1 for Yes, 0 or empty for No)", "Name", "SAP Role", "Competence Level", "Team", "Region", "Practice", "Highest Level#(lf) of Education", "Years of Work #(lf)Experience", "Years of work in Simulation ", "Years of work in Schneider/ Invensys Simulation Team", "FullTime Hour/Month", "May-21", "Jun-21", "Jul-21", "Aug-21", "Sep-21", "Oct-21", "Nov-21", "Dec-21", "Jan-22", "Feb-22", "Mar-22", "Apr-22"}, {"Employment Status#(lf)(1 for Yes, 0 or empty for No)", "Name.1", "SAP Role", "Competence Level", "Team", "Region", "Practice", "Highest Level#(lf) of Education", "Years of Work #(lf)Experience", "Years of work in Simulation ", "Years of work in Schneider/ Invensys Simulation Team", "FullTime Hour/Month", "May-21", "Jun-21", "Jul-21", "Aug-21", "Sep-21", "Oct-21", "Nov-21", "Dec-21", "Jan-22", "Feb-22", "Mar-22", "Apr-22"}),
Use:
= Table.ExpandTableColumn(#"Filtered Rows", "Content", Table.ColumnNames(Table.Combine(#"Filtered Rows"[Content])), Table.ColumnNames(Table.Combine(#"Filtered Rows"[Content])))
From this point, you should select the static columns and unpivot other columns, in order to create a correct tabular structure, what you have there is not right, sending data with expanding columns in Power Pivot data model is pretty useless.
As for the data model, a relationship can be added between 2 columns from 2 tables, but one table should not have duplicates, it is a "1 to many" relationship. In your case, AllDataRes has no duplicates in the Name column, so Power Pivot identifies this table as "1" and DataP as "many" - because it has duplicates in the related column, it's that simple.
You cannot enforce the direction based on what you think it should be, PP knows what is doing and why.
New Member
July 15, 2021
Offline
Hi Catalin,
Thanks for the reply. I don't want the data to be transposed after unpivoting the columns. Is there a way to do it as it is but with automation of month in the headers. Also, if you have an example pl. do share.
Also, for the data model when i'm trying to connect AllDataRes to AllDataR (Resource to Resource), it is connecting from AllDataR to AllDataRes. Is there an explanation for this?
-Thanks,
Shyam
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Also, for the data model when i'm trying to connect AllDataRes to AllDataR (Resource to Resource), it is connecting from AllDataR to AllDataRes. Is there an explanation for this?
I explained why, you just have to read the explanation.
1 Guest(s)
