Convert Stakeholder Contact Data in Rows to a Table with columns

Hi TvT  ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can handle with it in Power Query Editor by using Pivot function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZNda8IwFIb/SvDalZjqrrc5dzHoKLQXrs6LrJxpME3kUAv790uq7MPa1qEJpJzkFJKH8/AuFoMHkHIwrMuW44bEKAqOn4cWpSOzixEqUCUvRQUk4kKVoLjKwfx6er1hjIVmd4/5WpSQl2RWcGHvNOtZQ5Bovr1b2V6Q62Lf/1nLoROGSL8LCSReawX7l0YjSiitP08ML7wA8rajlN2SZIfKHL+nQuxUnHJMRSUkmamVUAD4V4oKHjV4cnLE0RTDmB8xRyAddhQx4znJgjzf2JdtBfVhAH7z2O6liYlwHvSYcQTRNBOGtZV2NY5IWtVESObuQdqTE2GWZV719KZnPPbp6NwMGVFmUqd4przi9iJTpAQUvKY5dC4PT5qmPX6uD9CUMpl0S7k+Q5cJMxOXEF1xSZLEk47eoPhy8o+ImOk0SJZf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emis Number" = _t, #"School Name" = _t, #"Project ID" = _t, #"Sub Programme" = _t, #"Rollout FY" = _t, #"Data Category" = _t, #"Ref No" = _t, #"Free Text" = _t, Unit = _t, Qty = _t, #"Rate(Incl VA)" = _t, Date = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emis Number", type text}, {"School Name", type text}, {"Project ID", type text}, {"Sub Programme", type text}, {"Rollout FY", type text}, {"Data Category", type text}, {"Ref No", type text}, {"Free Text", type text}, {"Unit", type text}, {"Qty", type text}, {"Rate(Incl VA)", type text}, {"Date", type text}, {"Time", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Ref No", "Unit", "Qty", "Rate(Incl VA)", "Date", "Time"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Stakeholder Discipline", each if Text.Contains([Data Category], "Architect") then "Architect" else if Text.Contains([Data Category], "Civil Engineer") then "Civil Engineer" else null),
    #"Replaced Value1" = Table.ReplaceValue(#"Added Custom","Architect ","",Replacer.ReplaceText,{"Data Category"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1","Civil Engineer ","",Replacer.ReplaceText,{"Data Category"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"Data Category"]), "Data Category", "Free Text")
in
    #"Pivoted Column"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards