Replacing values with data calculations

[350z]

Hi all,

I'm attempting to replace values of one column with calculations containing values from two other columns.

1. Replace all "Null" values within Column2 with ((Column5 / Column4)*100).

2. Replace all "Null" values within Column3 with ((Column7 / Column6)*100).

3. Replace all "Null Avg" values within Column2 with average percent of Column2.

4. Replace all "Null Avg" values within Column3 with average percent of Column3.

Any help would be much appreciated, thank you!

[dufoq3 · Accepted solution]

Hi @350z, as @lbendlin mentioned - for future requests provide sample data in usable format (not as screenshot) and expected result based on sample data. I haven't prepared exactly what you asked for, but check this - it is better for further analysis:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VFNC8IwDP0rYSAoaElbrdtR1MlgThE9jR0mm16GA53+frO24kfFu9A26evLa3hJUy9dbFa7dZQsMkBEGEAYQxjN45nX9z5X1k8dzMJxvq/PMK2LkpCwLBuITpcmr6qygG6nR+A6PzcXB93WdAVd0D00vS/FBjU8reFq6Q58Tg/JtaqegQ8DJgJKfGRqTHEk6ZDc8IXDF8iwVbGhJVuu72ojsdBo6ziiPXxY1JJgcju+p0IFTLZcrmxDSv+jfjn7MiCuB4QclpMo+bv5uH5Ln0lh/EbrN/528HtFdgc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    TrimmedTextColumn1 = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
    Ad_Grouping = Table.AddColumn(TrimmedTextColumn1, "Grouping", each if Text.StartsWith([Column1], "[Grouping", Comparer.OrdinalIgnoreCase) then [Column1] else null, type text),
    FilledDownGrouping = Table.FillDown(Ad_Grouping,{"Grouping"}),
    RemovedTopRows = Table.Skip(FilledDownGrouping, each [Column1] <> "Labor Code"),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRows, [PromoteAllScalars=true]),
    RenamedColumnGrouping = Table.RenameColumns(PromotedHeaders,{{List.Select(Table.ColumnNames(PromotedHeaders), (x)=> Text.Contains(x, "[Grouping]",Comparer.OrdinalIgnoreCase)){0}?, "Grouping"}}),
    ReplaceValues = Table.TransformColumns(RenamedColumnGrouping, {}, each if List.Contains({"Null", ""}, _) then null else _),
    ChangedType = Table.TransformColumnTypes(ReplaceValues, List.Transform(List.RemoveItems(Table.ColumnNames(ReplaceValues), {"Grouping"}) , (colName)=> {colName, type number}) & {{"Grouping", type text}}, "en-US"),
    RemovedErrorsLaborCode = Table.RemoveRowsWithErrors(ChangedType, {"Labor Code"}),
    FilteredRowsLaborCode = Table.SelectRows(RemovedErrorsLaborCode, each ([Labor Code] <> null)),
    ReplaceFeetInstalledPercent = Table.ReplaceValue(FilteredRowsLaborCode, 
        each [#"Feet Installed (%)"],
        each [#"Feet Installed (ft)"] / [#"Total Feet (ft)"],
        (x,y,z) => if y = null then z else x,
        {"Feet Installed (%)"} ),
    ReplacePartsInstalledPercent = Table.ReplaceValue(ReplaceFeetInstalledPercent, 
        each [#"Parts Installed (%)"],
        each [Parts Installed] / [Total Parts],
        (x,y,z) => if y = null then z else x,
        {"Parts Installed (%)"} ),
    GroupedRows = Table.Group(ReplacePartsInstalledPercent, {"Grouping"}, {{"All", each _, type table }, {"Feet Installed (%) Avg per Group", each List.Average([#"Feet Installed (%)"]), Percentage.Type}, {"Parts Installed (%) Avg per Group", each List.Average([#"Parts Installed (%)"]), Percentage.Type}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", List.RemoveItems(Table.ColumnNames(ReplacePartsInstalledPercent), {"Grouping"})),
    ChangedType2 = Table.TransformColumnTypes(ExpandedAll,{{"Labor Code", Int64.Type}, {"Feet Installed (%)", Percentage.Type}, {"Parts Installed (%)", Percentage.Type}, {"Total Feet (ft)", type number}, {"Feet Installed (ft)", type number}, {"Total Parts", Int64.Type}, {"Parts Installed", Int64.Type}})
in
    ChangedType2

[350z]

Thank you! A huge help!! A lot farther than I would've gotten and I will sure analyze the code you've provided for future help! However, I was hoping to group the report by Grouping Column Values (trimming "[GROUPING] " text from value is okay.

[dufoq3]

You're welcome.

[lbendlin]

your data is not in a usable format for Power BI.  Separate it into two queries.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[350z]

Thank you, I was having trouble uploading file. I will follow advice for next post!