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
- BA_Pete
Super User
You can restructure your data like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY+xCsMgEIZfRZwDOS9q07WZ26FryCBtaAuikgylb98oLVFQp4P7uO//bxwp4y1gi4AdbajgANvoIYyLJW5eVmuU9gwrTMIxz6ZmixBRhEw0J3UnV/VePYACECILglkm5Tssl2esxDhyXil/iCIYJCV/m31xVuZDBqvtQm5PZR7zurP/TZD2sRTrUv+xeTmns7bpCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Day shift made" = _t, #"Day Target" = _t, #"Error cause_DAY" = _t, #"Evening shift made" = _t, #"Evening Target" = _t, #"Error cause_EVENING" = _t, #"Night shift made" = _t, #"Night Target" = _t, #"Error cause_NIGHT" = _t]), buildDateCol = List.Repeat(Source[Date], 3), buildShiftCol = List.Repeat({"DAY"}, Table.RowCount(Source)) & List.Repeat({"EVENING"}, Table.RowCount(Source)) & List.Repeat({"NIGHT"}, Table.RowCount(Source)), buildMadeCol = Source[Day shift made] & Source[Evening shift made] & Source[Night shift made], buildTgtCol = Source[Day Target] & Source[Evening Target] & Source[Night Target], buildErrorCol = Source[Error cause_DAY] & Source[Error cause_EVENING] & Source[Error cause_NIGHT], buildTable = Table.FromColumns( {buildDateCol, buildShiftCol, buildMadeCol, buildTgtCol, buildErrorCol}, {"Date", "Shift", "Made", "Target", "errorCause"} ), chgTypes = Table.TransformColumnTypes(buildTable,{{"Date", type date}, {"Shift", type text}, {"errorCause", type text}, {"Made", Int64.Type}, {"Target", Int64.Type}}) in chgTypesWhich gives this output:
You can now easily write measures over the table:
_noofMade = SUM(yourTable[Made]) _madeTarget = SUM(yourTable[Target]) _varToTarget = [_noofMade] - [_madeTarget]Now, for your matrix, you just need to put [errorCause] as rows, [Shift] as columns, and [_varToTarget] as values, like this:
Pete