Forum Discussion
Merge multiple columns
No worries. Here's a different process to structure your data 'correctly' - it's not as quick or tidy, but should be much easier to understand/implement:
-1- In Power Query, right-click your shift table and choose 'Reference'.
-2- Change the name of this new query to 'dayShift' or similar.
-3- Select dayShift and multi-select (Ctrl+click) [Date], [Day shift made], [Day Target], and [Error cause_DAY].
-4- Right-click the header of one of the selected columns and choose 'Remove Other Columns'.
-5- Now double-click on the column header of [Day shift made] and change the name to 'Made'. Change [Day Target] to 'Target', change [Error cause_DAY] to 'errorCause'.
-6- Go to the Add Column tab > Custom Column. Call this new column 'Shift' and just enter "Day" as the formula.
-- Repeat steps 1-6 for [Date] + evening columns (eveningShift) and [Date] + night columns (nightShift).
-7- Select your dayShift query, go to the Home tab > Append Queries (dropdown) > Append Queries As New. Select 'Three or more tables' option and add your eveningShift and nightShift queries to the list.
-8- Rename your appended query to whatever you want and go through each of your original and 'shift' queries, right-clicking on them and UNchecking 'Enable Load'.
From here, all the measures/matrix setup should be exactly as I suggested before.
If you ever need to make changes to the query, you only need to make them in your original query and this will propogate through all the shift queries and into the appended query.
Pete
Super UserHi RVBIBS ,
In order to solve this issue, and avoid many more in the future, I'd recommend restructuring your data 'correctly' in Power Query.
Can you provide a copyable version of your outputs/errors/causes table please? I can then give you an example query to do the restructure.
Pete
Hej BA_Pete,
I'm pretty new to Power BI, so im not sure how i should end up were you are, based on the code you send. I can see you changed and add something in the query on the import, but the rest im clueless on. Do you maybe have a more beginner guide 😅
- BA_Pete
No worries. Here's a different process to structure your data 'correctly' - it's not as quick or tidy, but should be much easier to understand/implement:
-1- In Power Query, right-click your shift table and choose 'Reference'.
-2- Change the name of this new query to 'dayShift' or similar.
-3- Select dayShift and multi-select (Ctrl+click) [Date], [Day shift made], [Day Target], and [Error cause_DAY].
-4- Right-click the header of one of the selected columns and choose 'Remove Other Columns'.
-5- Now double-click on the column header of [Day shift made] and change the name to 'Made'. Change [Day Target] to 'Target', change [Error cause_DAY] to 'errorCause'.
-6- Go to the Add Column tab > Custom Column. Call this new column 'Shift' and just enter "Day" as the formula.
-- Repeat steps 1-6 for [Date] + evening columns (eveningShift) and [Date] + night columns (nightShift).
-7- Select your dayShift query, go to the Home tab > Append Queries (dropdown) > Append Queries As New. Select 'Three or more tables' option and add your eveningShift and nightShift queries to the list.
-8- Rename your appended query to whatever you want and go through each of your original and 'shift' queries, right-clicking on them and UNchecking 'Enable Load'.
From here, all the measures/matrix setup should be exactly as I suggested before.
If you ever need to make changes to the query, you only need to make them in your original query and this will propogate through all the shift queries and into the appended query.
Pete
