Summarize Multiple Columns

hello christiannobis 

please check if this accomodate your need.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVLBSgMxEP0V2XN9ZCaTzO5RKNKDB9Fj6aHqHoSyQu3+f5ONbtOaYBWGTAIz7715mfW66dhz2ywa8WAK+X7sdyE5kAspj9V2eNv14aJoY8fD9mUqJZhT6WO/v1m+f75+jMMhPG/ZxeLNIhBZVo3lCuGQl3fP4WQYveBJcRW6ByVs7pyPqg1Y8iGmV0VZB5LzsbytEcfIicyJxEBtDiPFca6FeeoP4344g5mRfekrJs2XuKLgHMzBUN0IhW0LoOmDLLhugwgoWxqBap3GwZVo/ul3wahf1ud7CWODpzTzF6aH6arCrYP/s25LbdQslJb9hxkCw3PLbJ+bbddke51kcwQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, #"Trans Amt" = _t, #"Charge 1 Des" = _t, #"Amount 1" = _t, #"Charge 2 Des" = _t, #"Amount 2" = _t, #"Charge 3 Des" = _t, #"Amount 3" = _t, #"Charge 4 Des" = _t, #"Amount 4" = _t, #"Charge 5 Des" = _t, #"Amount 5" = _t, #"Charge 6 Des" = _t, #"Amount 6" = _t, #"Charge 7 Des" = _t, #"Amount 7" = _t]),
#"Replaced Value3" = Table.ReplaceValue(Source,".",",",Replacer.ReplaceText,{"Order #", "Trans Amt", "Charge 1 Des", "Amount 1", "Charge 2 Des", "Amount 2", "Charge 3 Des", "Amount 3", "Charge 4 Des", "Amount 4", "Charge 5 Des", "Amount 5", "Charge 6 Des", "Amount 6", "Charge 7 Des", "Amount 7"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value3",{{"Order #", Int64.Type}, {"Trans Amt", type number}, {"Charge 1 Des", type text}, {"Amount 1", type number}, {"Charge 2 Des", type text}, {"Amount 2", type number}, {"Charge 3 Des", type text}, {"Amount 3", type number}, {"Charge 4 Des", type text}, {"Amount 4", type number}, {"Charge 5 Des", type text}, {"Amount 5", type number}, {"Charge 6 Des", type text}, {"Amount 6", type number}, {"Charge 7 Des", type text}, {"Amount 7", type number}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Amount 1", type text}}, "en-ID"),{"Charge 1 Des", "Amount 1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Amount 2", type text}}, "en-ID"),{"Charge 2 Des", "Amount 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.1"),
#"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Amount 3", type text}}, "en-ID"),{"Charge 3 Des", "Amount 3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.2"),
#"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"Amount 4", type text}}, "en-ID"),{"Charge 4 Des", "Amount 4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.3"),
#"Merged Columns4" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns3", {{"Amount 5", type text}}, "en-ID"),{"Charge 5 Des", "Amount 5"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.4"),
#"Merged Columns5" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns4", {{"Amount 6", type text}}, "en-ID"),{"Charge 6 Des", "Amount 6"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.5"),
#"Merged Columns6" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns5", {{"Amount 7", type text}}, "en-ID"),{"Charge 7 Des", "Amount 7"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged.6"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns6", {"Order #"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type text}}, "en-ID"), "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type number}}),
#"Replace Value1" = Table.ReplaceValue(#"Changed Type1", each [Attribute], each if not Text.StartsWith([Attribute], "Trans") then "" else [Attribute], Replacer.ReplaceText,{"Attribute"}),
#"Merged Columns7" = Table.CombineColumns(Table.TransformColumnTypes(#"Replace Value1", {{"Value.2", type text}}, "en-ID"),{"Attribute", "Value.1", "Value.2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Replaced Value" = Table.ReplaceValue(#"Merged Columns7"," ",":",Replacer.ReplaceText,{"Merged"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",";"," ",Replacer.ReplaceText,{"Merged"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Merged", Text.Trim, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Merged", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged.1", type text}, {"Merged.2", type number}}),
#"Replaced Value2" = Table.ReplaceValue(#"Changed Type2",":"," ",Replacer.ReplaceText,{"Merged.1"})
in
#"Replaced Value2"

the M is kind of a mess, but perhaps gives you an idea where to start.

You can tweak the m-code as your preferences.

Hope this will help.

Thank you.

@Hi christiannobis 

Lets achieve this by using Power Query in Power BI Desktop. 

Open power bi desktop and go to power query editor. 

Take blank query and then go to advance editor and pest this m-code 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVLBSgMxEP0V2XN9ZCaTzO5RKNKDB9Fj6aHqHoSyQu3+f5ONbtOaYBWGTAIz7715mfW66dhz2ywa8WAK+X7sdyE5kAspj9V2eNv14aJoY8fD9mUqJZhT6WO/v1m+f75+jMMhPG/ZxeLNIhBZVo3lCuGQl3fP4WQYveBJcRW6ByVs7pyPqg1Y8iGmV0VZB5LzsbytEcfIicyJxEBtDiPFca6FeeoP4344g5mRfekrJs2XuKLgHMzBUN0IhW0LoOmDLLhugwgoWxqBap3GwZVo/ul3wahf1ud7CWODpzTzF6aH6arCrYP/s25LbdQslJb9hxkCw3PLbJ+bbddke51kcwQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order #" = _t, #"Trans Amt" = _t, #"Charge 1 Des" = _t, #"Amount 1" = _t, #"Charge 2 Des" = _t, #"Amount 2" = _t, #"Charge 3 Des" = _t, #"Amount 3" = _t, #"Charge 4 Des" = _t, #"Amount 4" = _t, #"Charge 5 Des" = _t, #"Amount 5" = _t, #"Charge 6 Des" = _t, #"Amount 6" = _t, #"Charge 7 Des" = _t, #"Amount 7" = _t]),


    
    // Get column names that contain "Charge" and "Amount" with error handling
    ColumnNames = Table.ColumnNames(Source),
    ChargeColumns = List.Select(ColumnNames, each Text.Contains(_, "Charge") and Text.Contains(_, "Des")),
    AmountColumns = List.Select(ColumnNames, each Text.Contains(_, "Amount")),
    
    // Ensure we have matching pairs
    PairCount = List.Min({List.Count(ChargeColumns), List.Count(AmountColumns)}),
    PairIndexes = if PairCount > 0 then {1..PairCount} else {},
    
    // Function to process each pair with error handling
    ProcessPair = (tbl as table, index as number) =>
        let
            ChargeCol = try ChargeColumns{index-1} otherwise null,
            AmountCol = try AmountColumns{index-1} otherwise null
        in
            if ChargeCol <> null and AmountCol <> null and 
               Table.HasColumns(tbl, {ChargeCol, AmountCol}) then
                try Table.CombineColumns(tbl, {ChargeCol, AmountCol}, 
                    Combiner.CombineTextByDelimiter("|", QuoteStyle.None), 
                    "Pair_" & Number.ToText(index)) 
                otherwise tbl
            else tbl,
    
    // Apply to all pairs with error handling
    ProcessedTable = if List.Count(PairIndexes) > 0 then
        List.Accumulate(PairIndexes, Source, ProcessPair)
    else Source,
    
    // Check if we have any pairs to unpivot
    ProcessedColumnNames = Table.ColumnNames(ProcessedTable),
    PairColumnsToUnpivot = List.Select(ProcessedColumnNames, each Text.StartsWith(_, "Pair_")),
    
    // Only proceed with unpivot if we have pairs
    UnpivotedColumns = if List.Count(PairColumnsToUnpivot) > 0 then
        let
            IdentifierCols = {"Order #"},
            ValidIdentifierCols = List.Select(IdentifierCols, each Table.HasColumns(ProcessedTable, {_}))
        in
            if List.Count(ValidIdentifierCols) > 0 then
                Table.UnpivotOtherColumns(ProcessedTable, ValidIdentifierCols, "Attribute", "Value")
            else
                Table.Unpivot(ProcessedTable, PairColumnsToUnpivot, "Attribute", "Value")
    else
        ProcessedTable,
    
    // Split and clean only if we have unpivoted data
    SplitColumns = if Table.HasColumns(UnpivotedColumns, {"Value"}) then
        try Table.SplitColumn(UnpivotedColumns, "Value", 
            Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
            {"Charge_Des", "Amount"}) 
        otherwise UnpivotedColumns
    else UnpivotedColumns,
    
    // Filter and clean
    FilteredRows = if Table.HasColumns(SplitColumns, {"Charge_Des"}) then
        Table.SelectRows(SplitColumns, each 
            [Charge_Des] <> null and 
            [Charge_Des] <> "" and 
            [Charge_Des] <> "null")
    else SplitColumns,
    #"Added Conditional Column" = Table.AddColumn(FilteredRows, "Final Amount", each if [Amount] = null then [Charge_Des] else [Amount]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Fina Charge_Des", each if [Attribute] = "Trans Amt" then [Attribute] else [Charge_Des]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Attribute", "Charge_Des", "Amount"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Final Amount", "Amount"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Amount", type number}, {"Fina Charge_Des", type text}})
in
    #"Changed Type1"

Results: 

You can download the answered file from here: https://limewire.com/d/Xv74q#0Jvro2iEVu 

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Hi,

Using this Power Query code, transform your data into a 3 column table

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromRows(List.Split(List.Skip(Record.ToList(_),2),2),{"Description","Amount"}))[[#"Order #"],[Custom]],
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Description", "Amount"}, {"Description", "Amount"}),
    Custom1 = Table.SelectRows(#"Expanded Custom.1", each [Description]<>null and [Amount]<>null),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Order #", type text}, {"Description", type text}, {"Amount", type number}})
in
    #"Changed Type"

Yu should now be able to build your desired visual.

Hope this helps.