Merge multiple columns

[RVBIBS]

I'm trying to find a smart way to merge one data table with mulitple columns from another table - Or make a connection.

I have a data table only with error causes (ErrorCauses)

And then i have a table with output, targets and error causes if target isn't achieved, diviveded 3 shifts. (DATAOutput)

I want to make a matrix with the list of error causes and then the amount under target based on error causes.

I have already made a calculation, so i have the difference MADE minus TARGET pr. shift.

But i can't seem to get the matrix show what i want to, because i can only make a link from ErrorCause to one shift in DATAOutput - Example Day shift.

I know i can solve this by making mulitple data sheets for each shift, but i hope there was a another way.

This matrix isn't showing all the other error causes from evening or night shifts, because its only linked to day shift column.

[RVBIBS]

Hej @BA_Pete 

Here is the output and error causes data:

https://filetransfer.io/data-package/Atc2onmp#link

[BA_Pete]

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
    chgTypes

Which 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

[BA_Pete]

Hi @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

[RVBIBS]

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 · Accepted solution]

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

[RVBIBS]

This works like a charme! Thank you so much @BA_Pete. I can see i have a lot more to discover in Power BI, this is a completly new world.