Forum Discussion

dommyw277's avatar
dommyw277
Helper V
8 months ago
Solved

Splitting Column values

Hi, how would i split a column like this:   Category1 SR-01 SR-02 SR-03 Category 2 SR-05 INC-01 INC-02 Category 3 INC-03 SR-06 etc etc
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    8 months ago

    Hi dommyw277 ,

    Thanks for the screenshot  that really helps. As per my guess, the Invalid identifier error is coming from the way the column name Inc/SR is being referenced. Because the column name contains a slash (/), Power Query requires the #""  format.

    So anywhere you have- [Inc/SR]

    please change it to - [#"Inc/SR"]

    And in your filter step

    each [#"Inc/SR"] <> null and [#"Inc/SR"] <> ""

    Once the column is referenced with [#"...."] , the syntax error should be resolved.


    Hope this helps please give it a try. If anything still comes up, feel free to share another screenshot and we can take a look.

dommyw277's avatar
dommyw277
Helper V
8 months ago

So the file is a csv not an excel document so I cahnged the code to below however, i get an error :

 

let
Source = Csv.Document(){[Name="Data"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Inc/SR", each if Text.StartsWith([Remarks],"SR-",Comparer.OrdinalIgnoreCase) or Text.StartsWith([Remarks],"INC-",Comparer.OrdinalIgnoreCase) then [Remarks] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", each if [#"Inc/SR"]=null then [Remarks] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Category"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [#"Inc/SR"] <> null and [#"Inc/SR"] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Remarks"})
in
#"Removed Columns"

 

Error:

Expression.Error: 0 arguments were passed to a function which expects between 1 and 5.
Details:
Pattern=
Arguments=[List]

V-yubandi-msft's avatar
V-yubandi-msft
Community Support
8 months ago

Hi dommyw277 ,

Thanks for clarifying that the source file is a CSV.

The error you’re seeing is expected with this line:

Source = Csv.Document(){[Name="Data"]}[Content],

Unlike Excel, a CSV file doesn’t contain named tables (such as Data).

Csv.Document must be provided with the file contents, so referencing a table name will result in the error.

0 arguments were passed to a function which expects between 1 and 5

To resolve this, please use the Source step that Power Query automatically generates when you import the CSV file, and then apply the remaining transformation steps (Add Column, Fill Down, Filter, etc.) on top of that source.

Also, please double check the exact column name (for example, Remarks vs Coloum1) and ensure headers are promoted correctly.

 

Once the source step is corrected, the rest of the logic should work as expected.

 

Hope this helps..