Hi, I am trying to learn power query. Right now I am at the "train with pain" stage. I am able to import three sheets from three external workbooks and modify them so they create three tables in excel. I resisted the urge to just do xlookups to create helpers so I could make pivot tables because easy doesn't teach. But I might have misunderstood any of the myriad of youtubes and google articles.
Each table has employee names formatted as Lname comma space firstname - One table is basic employee data showing who they are, where they work, and who they work for.
One table has the same names but with a different column header. It shows what they were issued, when, what office, and what person. I change the date to drop the time so it is just a calendar day. If I were doing Access, dBaseIII, or Approach this would be the "many" table. It is related to the employee table with that being the "one" as in one-to-many.
I created a relationship but it doesn't appear to be linking as I can't figure out how to pivot table using aspects of both tbales. So, another youtube later and I got power pivot going and added to the data model but I now see four tables when trying to make a pivot instead of two. I picked two at random and got a pivot. But, I cannot group the columns (dates) by days. Usually we do 7 or 10 day blocks on a 70 day dataset. The option to group by days is there but the start/end and value of how many isn't functional. I checked the date format and it is dd/mm/yyyy and the time aspect was removed by the query during the transform. There are no blanks or text in the source column that I can tell.
I don't expect answers that provide a finished result. I am looking for insights into the above that I can used to key in on tutorials or videos that will help me understand what I obviously didn't when just diving into it. Thank you very much for looking and any advice is greatly appreciated.
Each table has employee names formatted as Lname comma space firstname - One table is basic employee data showing who they are, where they work, and who they work for.
One table has the same names but with a different column header. It shows what they were issued, when, what office, and what person. I change the date to drop the time so it is just a calendar day. If I were doing Access, dBaseIII, or Approach this would be the "many" table. It is related to the employee table with that being the "one" as in one-to-many.
I created a relationship but it doesn't appear to be linking as I can't figure out how to pivot table using aspects of both tbales. So, another youtube later and I got power pivot going and added to the data model but I now see four tables when trying to make a pivot instead of two. I picked two at random and got a pivot. But, I cannot group the columns (dates) by days. Usually we do 7 or 10 day blocks on a 70 day dataset. The option to group by days is there but the start/end and value of how many isn't functional. I checked the date format and it is dd/mm/yyyy and the time aspect was removed by the query during the transform. There are no blanks or text in the source column that I can tell.
I don't expect answers that provide a finished result. I am looking for insights into the above that I can used to key in on tutorials or videos that will help me understand what I obviously didn't when just diving into it. Thank you very much for looking and any advice is greatly appreciated.
