Attempting to split decimal numbers in batch using a prevo=ious formula provided on here however the result is an error stating that null or "" or "x" (where is a number) cant be converted to the type list.

The formula:

=try Text.Remove([Column1],Text.ToList(Text.Remove([Column1],{"0".."9","."}))) otherwise null works when applied to a single column however when trying to create a create a table from these columns I get the followings errors:

Desired Output:

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromColumns({
        
    (try Text.Remove([Column1],Text.ToList(Text.Remove([Column1],{"0".."9","."}))) otherwise null), 
    
    (try Text.Remove([Column2],Text.ToList(Text.Remove([Column2],{"0".."9","."}))) otherwise null)
    
    }))
in
    #"Added Custom"

I would like to be able to generate a Table.FromColumns, for n columns which I can then expand. This is just an example and in reality, the number of columns can vary quite a lot.

Update

To better visualise what I am trying to do in power query I wish to create this scenario:

Such that this table can be expanded to:

Probably something obvious but any help appreciated.

CodePudding user response：

Edited to be usable for multiple columns

let Source =Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Value.2"}),
#"rename1" = Table.TransformColumns(#"Removed Columns",{{"Attribute", each _&"a", type text}}),
#"Pivoted Column" =  Table.RemoveColumns(Table.Pivot(#"rename1", List.Distinct(#"Lowercased Text"[Attribute]), "Attribute", "Value.1"),{"Index"}),
#"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter",{"Value.1"}),
rename = Table.TransformColumns(#"Removed Columns2",{{"Attribute", each _ & "b", type text}}),
#"Pivoted Column1" = Table.RemoveColumns(Table.Pivot(rename, List.Distinct(rename[Attribute]), "Attribute", "Value.2"),{"Index"}),
TFC = Table.FromColumns(Table.ToColumns(Source)&Table.ToColumns(#"Pivoted Column")&Table.ToColumns(#"Pivoted Column1"),Table.ColumnNames(Source)&Table.ColumnNames(#"Pivoted Column")&Table.ColumnNames(#"Pivoted Column1"))
in TFC

CodePudding user response：

I would just

• split the columns based on character transition, including the decimal in the character list.
• Then Trim the resultant columns to remove any leading/following spaces

Note: Code edited to allow for any number of columns to be split in two. Column names can be dynamic also

let
    Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),

//Generate new table from all the columns
    //create List of columns
        colList = Table.ToColumns(#"Changed Type"),
        colNames = Table.ColumnNames(#"Changed Type"),

    //convert each column
        splitCols = List.Generate(
            ()=>[colPair=
                    List.Transform(colList{0},(li)=>
                        Splitter.SplitTextByCharacterTransition(
                            {"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c))
                            (li)), 
                 cn = colNames{0},
                 idx=0],
            each [idx] < List.Count(colList),
            each [colPair=
                    List.Transform(colList{[idx] 1},(li)=>
                        Splitter.SplitTextByCharacterTransition(
                            {"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c))
                            (li)), 
                   cn=colNames{[idx] 1},
                   idx=[idx] 1],
            each  List.Zip([colPair]) & {List.Transform({1..2}, (n)=> [cn] & "." & Text.From(n))}),
    newCols = List.Combine(List.Transform(splitCols, each List.RemoveLastN(_,1))),
    newColNames = List.Combine(List.Transform(splitCols, each List.Last(_))),
    newTable = Table.FromColumns(newCols,newColNames),

//trim the excess spaces
    trimOps = List.Transform(Table.ColumnNames(newTable), each {_, Text.Trim}),
    trimAll = Table.TransformColumns(newTable, trimOps)
in
    trimAll

Example with three columns

Again, if you want to retain the original columns in your result table, you need to change three lines in the code:

...
    newCols = Table.ToColumns(#"Changed Type") & List.Combine(List.Transform(splitCols, each List.RemoveLastN(_,1))),
    newColNames = Table.ColumnNames(#"Changed Type") & List.Combine(List.Transform(splitCols, each List.Last(_))),
    newTable = Table.FromColumns(newCols,newColNames),
...

CodePudding user response：

I would just duplicate the two original columns (Add Column > Duplicate column) and then split the resulting columns on the left most " " delimiter. No M code needed.