Forum Discussion
Merging data monthly dataset with identical columns.
Hello! I'm new to the forum and Power BI so I thank you in advance for your patience with me.
I'm confused on how to implement this data merge. We get annual Page Hit stats from a portal vendor on a monthly basis and the layout of the Excel dataset is like this every month (please excuse the crappy table layout):
| orgid | orgname | groupnum | programid | programname | serviceid | servicename | m1 | m2 | m3 | m4 | m5 | m6 | m7 | m8 | m9 | m10 | m11 | m12 | startdate |
| 3af5fd51 | Production Org | 05 | 1956eb62 | Member Plan Information | d3ec1317 | Plan Info | 7 | 6 | 6 | 12 | 7 | 8 | 7 | 8 | 11 | 12 | 19 | 16 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 79d3ea22 | Member Home | b6fde6b1 | Flex All | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 2 | 3 | 0 | 6 | 6 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 79d3ea22 | Member Home | 74bd6a99 | Home | 22 | 12 | 18 | 19 | 20 | 18 | 24 | 20 | 26 | 21 | 48 | 24 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 4f591cbc | Member Express Requests | 8890f235 | Member Requests | 3 | 0 | 4 | 7 | 0 | 7 | 5 | 0 | 1 | 4 | 14 | 1 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 4f347d9f | Member FAQs | 94c96095 | FAQ | 5 | 2 | 6 | 6 | 4 | 2 | 8 | 3 | 3 | 8 | 14 | 0 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 742f50e4 | NEW Member | 8381064b | Login | 16 | 10 | 24 | 17 | 23 | 26 | 28 | 23 | 26 | 19 | 44 | 62 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 742f50e4 | NEW Member | 34a52335 | Profile | 3 | 2 | 6 | 3 | 4 | 5 | 4 | 1 | 2 | 2 | 8 | 4 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 742f50e4 | NEW Member | 55519f93 | Home | 11 | 9 | 12 | 7 | 9 | 12 | 14 | 11 | 13 | 9 | 20 | 13 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | 742f50e4 | NEW Member | a6427273 | Messages | 14 | 0 | 4 | 18 | 5 | 17 | 31 | 1 | 14 | 12 | 42 | 4 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | f76dba1c | Member Eligibility | 59bc3d26 | Coverage | 14 | 4 | 5 | 10 | 8 | 5 | 12 | 4 | 4 | 9 | 18 | 18 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | f76dba1c | Member Eligibility | b2d6b01a | Claims | 3 | 3 | 4 | 6 | 5 | 2 | 5 | 1 | 7 | 3 | 8 | 3 | 3/1/2021 |
| 3af5fd51 | Production Org | 05 | f76dba1c | Member Eligibility | 18c33b6c | Accumulators | 2 | 2 | 0 | 1 | 6 | 5 | 8 | 4 | 3 | 4 | 5 | 3 | 3/1/2021 |
| 3af5fd51- | Production Org | 05 | ed370219 | Member Forms | 40a09818 | Forms | 8 | 7 | 5 | 10 | 4 | 6 | 10 | 4 | 7 | 6 | 23 | 6 | 3/1/2021 |
| 3af5fd51 | Production Org | 12 | 1956eb62 | Member Plan Information | d3ec1317 | Plan Info | 224 | 156 | 124 | 174 | 175 | 202 | 192 | 199 | 336 | 319 | 297 | 231 | 3/1/2021 |
| 3af5fd51 | Production Org | 12 | 79d3ea22 | Member Home | b6fde6b1 | Flex All | 56 | 34 | 26 | 41 | 50 | 35 | 55 | 41 | 38 | 59 | 106 | 71 | 3/1/2021 |
| 3af5fd51 | Production Org | 12 | 79d3ea22 | Member Home | 74bd6a99 | Home | 623 | 452 | 393 | 459 | 527 | 550 | 524 | 464 | 675 | 763 | 832 | 690 | 3/1/2021 |
| 3af5fd51 | Production Org | 12 | 4f591cbc | Member Express Requests | 8890f235 | Member Requests | 144 | 88 | 58 | 85 | 72 | 79 | 54 | 62 | 114 | 147 | 119 | 118 | 3/1/2021 |
| 3af5fd51 | Production Org | 12 | 4f347d9f | Member FAQs | 94c96095 | FAQ | 130 | 86 | 64 | 97 | 106 | 93 | 81 | 87 | 134 | 134 | 116 | 116 | 3/1/2021 |
| 3af5fd51 | Production Org | 12 | 73431dc8 | Provider | b1a8e6d8 | Messages | 9 | 1 | 1 | 0 | 0 | 0 | 18 | 1 | 11 | 8 | 0 | 8 | 3/1/2021 |
| 3af5fd51 | Production Org | 12 | 73431dc8 | Provider | 24d065ff | Home | 28 | 22 | 7 | 10 | 5 | 3 | 7 | 1 | 10 | 20 | 5 | 15 | 3/1/2021 |
I'm still working on the data clean up, and there are more rows of data. Some records have null in the programid, programname, serviceid, and servicename collumns. The "m#" columns are the preceding months in the past year. For example, m12 is the month beginning with the start date, m11 the month before, etc. and m1 is last month (Feb 2022).
When I retrieve the dataset (same filename, same column names) next month, I want to import ONLY the m1 column but also making sure it matches up with the id fields so the values are populated in the correct groupnum, serviceid, etc.
Hopefully I've explained this one clearly as I'm really stumped how to implement this.
Hi Rakhir ,
Please refer to my pbix to see if it helps you.
Add the index to the table.
Then create a column.
Column = LOOKUPVALUE('Table'[m1],'Table'[Index],'Table _next'[Index])If I have misunderstood your meaning, please provide more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Rakhir ,
Please refer to my pbix to see if it helps you.
Add the index to the table.
Then create a column.
Column = LOOKUPVALUE('Table'[m1],'Table'[Index],'Table _next'[Index])If I have misunderstood your meaning, please provide more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
