Convert Stakeholder Contact Data in Rows to a Table with columns

[TvT]

Hi All

I have a table called "Project Data Tracker" as follows:

I would like to ceate a new table in Power Bi Based on the "Project Data Tracker" table but it must look as follows:

Is this possible? 

[v-yiruan-msft]

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

[TvT]

Hi @v-yiruan-msft 

Thanks for your response. 

I pasted the new code that you provided but unfortunately, it is still not working for me. I get the same error.

The query "Test" draws data from Multiple Excel files under the folder "xx\QS PSP Cost Report Data Trackers". There are approximately 29 excel files (All formatted exactly the same- including same table name "Project Data Tracker") . The folder "xx\QS PSP Cost Report Data Trackers" is a Sharepoint folder which is synced to my PC. 

Power Bi points to the synced folder on my PC and it combines all the data from the 29 excel files into one single table using power query.  I believe the purpose of the yellow text in the screenshot that you questioned is to combine the data from all those excel files into one table (I think...)

I would gladly share the Power Bi file with you but it contains alot of information and there are also a number of other tables etc that feed into the file with numerous relationships. It would take me a long time to clean the file for sharing here but i will have a look and see if I can make one that is shareable on this forum.

[TvT]

Hi @v-yiruan-msft 

Thank you so much for your reply.

I have tried to paste your code into my query editor. However, I am getting an error.

I think its becuase my "Source" is different from your Source. Please see below screenshot, where I pasted your code to try and make it work and the resultant error:

[v-yiruan-msft]

Hi @TvT ,

The query "Test" is from one file under the folder "xx\QS PSP Cost Report Data Trackers"? If yes, could you please provide the related file(exclude sensitive info in it)? By the way, the part marked with yellow in the below screenshot, what's the function? Thank you.

In addition, please try to update the applied codes for the query "Test" as below and check if it can works...

let
    Source = Folder.Files(WoringFolder & "\QS PSP Cost Report Data Trackers"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoked Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoked Custom Function1", {"Name","Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1" ,{"Source.Name","Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1","Transform File (2)",Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"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"

Best Regards