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 list of 'projects' that have employee's assigned to them from a merged Group ID query. My issue is I need to replace all the nulls in the Employee column with names from the 'Employee' list I have, and the names should appear an equal amount of times when all is said and done. Can probalby be accomplished in Powerpivot, but is it possible in Power Query. Screenshots attached for reference. thank you.
Solved! Go to Solution.
@meklundcan you please give this a try
let
LookupTable=let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNU0lEyVIrVATKNgEwjCNMYyDRWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Index", Int64.Type}})
in
#"Changed Type",
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNU0lEyNDBQitUBcoyAHGMoB8g0gbNMEYJmCKY5TJ8xkGMB45gAOZYIRYYGOE2JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, val = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"val", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, LookupTable, {"Column1"}, "index", JoinKind.LeftOuter),
#"Expanded index" = Table.ExpandTableColumn(#"Merged Queries", "index", {"Column1"}, {"Column1"}),
#"Added Index1" = Table.AddIndexColumn(#"Expanded index", "Index.1", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each if [Column1]=null then
let x=List.Count(LookupTable[Column1]),
z = Number.Mod([Index.1],x),
z1 = LookupTable[Column1]{z} in z1 else [Column1]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "MergedValue"}, {"Custom", "ConditionalValue"}})
in
#"Renamed Columns"
Most of the binary steps went over my head 😅 but I was able to adapt everything after the index and got what I needed. Thank you very much!!
Are you wanting to randomly assign employees to Projects that have blanks in the Employee column? If not, please explain the logic needed to fill in blank values (the list shown only has Employee names, not project assignments).
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Correct. Names can be assigned randomly to blank values in the Employee column, the result being that the count of names already merged plus the ones we are randomly assigning to blank rows are distibuted as equally as possible. Thanks for the help!
@meklundcan you please give this a try
let
LookupTable=let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNU0lEyVIrVATKNgEwjCNMYyDRWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Index", Int64.Type}})
in
#"Changed Type",
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNU0lEyNDBQitUBcoyAHGMoB8g0gbNMEYJmCKY5TJ8xkGMB45gAOZYIRYYGOE2JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, val = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"val", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, LookupTable, {"Column1"}, "index", JoinKind.LeftOuter),
#"Expanded index" = Table.ExpandTableColumn(#"Merged Queries", "index", {"Column1"}, {"Column1"}),
#"Added Index1" = Table.AddIndexColumn(#"Expanded index", "Index.1", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each if [Column1]=null then
let x=List.Count(LookupTable[Column1]),
z = Number.Mod([Index.1],x),
z1 = LookupTable[Column1]{z} in z1 else [Column1]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "MergedValue"}, {"Custom", "ConditionalValue"}})
in
#"Renamed Columns"
Followup question. The 'New Merge' I did gave some employees higher numbers since they already had more in the inital merge. Is there a way to even this out and maybe skip a name if it appears higher than the average between all employees? Thanks again!!!
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.