Power Query Split Column Dynamically

[vilnukoy]

I have the following code for splitting a column:

let
Source = Folder.Files("C:\Users\XXXX\Desktop\Athena Reports"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table Column1", each true),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"S:No", "Origin ID", "Origin Name"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Check Number", "Check Number - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column1","#","",Replacer.ReplaceText,{"Check Number - Copy"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Check Number - Copy", Text.Trim, type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "RCM Posting History", "RCM Posting History - Copy"),
#"Filtered Rows1" = Table.SelectRows(#"Duplicated Column", each true),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Filtered Rows1"),
SplitByDelimiter = (table, column, delimiter) =>
let
Count = List.Count(List.Select(Text.ToList(Table.Column(table, column){0}), each _ = delimiter)) + 1,
Names = List.Transform(List.Numbers(1, Count), each column & "." & Text.From(_)),
Types = List.Transform(Names, each {_, type text}),
Split = Table.SplitColumn(table, column, Splitter.SplitTextByDelimiter(delimiter), Names),
Typed = Table.TransformColumnTypes(Split, Types)
in
Typed,
#"Split Column by Delimiter" = SplitByDelimiter("RCM Posting History - Copy","~")
in
#"Split Column by Delimiter"

BUT keep getting the following error:

Expression.Error: 2 arguments were passed to a function which expects 3.
Details:
Pattern=
Arguments=

• 
  
  Can anybody help on this??

 

[vilnukoy]

Example of info in RCM Posting History - Copy

10/11/2023 04:33:02 PM~CGutierrez~Manual~REVERSED PAYMENT ERROR~07/03/2023~($23.46)~SUCCESSFUL|10/11/2023 04:32:01 PM~CGutierrez~Manual~COVID PAYMENT~07/03/2023~$23.46~SUCCESSFUL|10/11/2023 04:31:34 PM~CGutierrez~Manual~COVID PAYMENT~07/03/2023~$23.46~SUCCESSFUL|

 

[JEC]

Your custom function needs 3 variables. You used just 2 in the line below

SplitByDelimiter("RCM Posting History - Copy","~")

 

[vilnukoy]

Your custom function needs 3 variables. You used just 2 in the line below

SplitByDelimiter("RCM Posting History - Copy","~")

And how do I fix that?

 

[GraH]

You need to provide table, column and delimiter according to the input definition of the function M-code. You gave it a table and the delimiter.

 

[vilnukoy]

You need to provide table, column and delimiter according to the input definition of the function M-code. You gave it a table and the delimiter.

Can you point that out in the code?

 

[GraH]

After that line SplitByDelimiter...

 

[vilnukoy]

After that line SplitByDelimiter...

RCM Posting History XXXXX is the column and not table. What would be the table and on what position do I need to put it?

 

[GraH]

SplitByDelimiter = (table, column, delimiter) =>
Table would be previous step I guess.