Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have a table with three columns that I am trying to reformat but cannot figure out the correct steps to achieve the desired result. The table consists of columns titled "project", "WBS", and "resource name" that has all values in text format. In situations where 2 of the columns are identical, I would like to combine the values in the adjacent third column in a specified manner. Please see below for example:
Current Table
project WBS resource name
MAIN01 1.04.01.01 Jane Doe
MAIN01 1.04.02.01 John Smith
MAIN01 1.04.02.01 Bob Johnson
MAIN01 1.04.02.01 Jane Doe
MAIN01 1.04.03.01 John Smith
FACL02 1.05.02 James Jones
FACL02 1.05.02 Todd Doe
FACL02 1.05.03 Jane Doe
FACL02 1.05.04 Bob Johnson
FACL02 1.05.04 Todd Doe
Desired Outcome:
project WBS resource name
MAIN01 1.04.01.01 Jane Doe
MAIN01 1.04.02.01 John Smith, Bob Johnson, Jane Doe
MAIN01 1.04.03.01 John Smith
FACL02 1.05.02 James Jones, Todd Doe
FACL02 1.05.03 Jane Doe
FACL02 1.05.04 Bob Johnson, Todd Doe
Thank you in advance for any assistance provided as once I can resolve this hurdle my reporting times will be greatly reduced!
Kind Regards,
Irv
Solved! Go to Solution.
Hi @Anonymous
A simple Group by will do:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nX09DMwVNJRMtQzMNEzAJIgjldiXqqCS36qUqwOhhIjqJL8jDyF4NzMkgw8ipzykxRACovz8/AZhcc2Y6y2uTk6+xgYQRSZ6oFZXom5qcVAy/JSi3EpCclPSYHbgyZvjO4ONHkTLL7BogRhRSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [project = _t, WBS = _t, #"resource name" = _t]),
#"Grouped Rows" = Table.Group(Source, {"project", "WBS"}, {{"resource name", each Text.Combine([resource name], ", "), type text}})
in
#"Grouped Rows"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi @Anonymous
A simple Group by will do:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nX09DMwVNJRMtQzMNEzAJIgjldiXqqCS36qUqwOhhIjqJL8jDyF4NzMkgw8ipzykxRACovz8/AZhcc2Y6y2uTk6+xgYQRSZ6oFZXom5qcVAy/JSi3EpCclPSYHbgyZvjO4ONHkTLL7BogRhRSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [project = _t, WBS = _t, #"resource name" = _t]),
#"Grouped Rows" = Table.Group(Source, {"project", "WBS"}, {{"resource name", each Text.Combine([resource name], ", "), type text}})
in
#"Grouped Rows"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi @Anonymous ,
Try this m code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nX09DMwVFDSUTLUMzDRMwCShkCOV2JeqoJLfqpSrA6mGiOomvyMPIXg3MySDHyqnPKTFEAqi/Pz8BqGz0JjrBa6OTr7GBhBVZkCTVJQUACblJtaDLQyL7UYn7KQ/JQUuIXoaoxhRiE5Cl2NCUQNuv9wKENYFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, #"WBS " = _t, #"resource name" = _t]),
#"Trimmed Text" = Table.TransformColumns(Source,{{"resource name", Text.Trim, type text}, {"WBS ", Text.Trim, type text}, {"project", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"project", "WBS "}, {{"Rows", each _, type table [resource name=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Rows][resource name], ", ")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type text}})
in
#"Changed Type"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.