Forum Discussion

TvT's avatar
TvT
Regular Visitor
3 years ago

Convert Stakeholder Contact Data in Rows to a Table with columns

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
TvT
Regular Visitor
3 years ago

Hi Anonymous 

 

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:

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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