Forum Discussion

TimTeska's avatar
TimTeska
Frequent Visitor
2 years ago
Solved

Creating a new column using the contents from another cell.

Hello Experts,

I'm having trouble with something I think should be very straight forward. I am new to Power Query Editor and still learning DAX, M and I have no experience with JSON.

During a load and transform data in PBI Power Query Editor - I'm trying to create a new column called "Period" using some of the data from Column1 row 4 (or indexed row 3), and I only want the data to the left of the "," in that field. More detail below.

Background:

The Excel file I'm importing is a financial report, In Column1 (formatted as Text), the first 4 rows contain standard report data: the date stamp, the company name, the report, and then the period and type of report separated by a ",".

My objective:

I'm trying to create a dashboard that is linked to an Excel file Report. This dashboard needs to get updated every quarter or more. When I want to update the data in the dashboard, I want to just overwrite the old Excel report with the newest and refresh my dashboard, and let the PBI Power Query Editor run the steps I used to format the file in the final Query for the Dashboard.

The steps I used to create this new Column called "Period" using all data to the left of the comma in Column1 row 4.  In that field is "2023_Close_Q1, CSC0002_Region_JnJ GAAP" and I only want "2023_Close_Q1" to be reflected in the new Period Column See Screenshot.

My outcome:

I tried to insert a custom column, named it "Period" and for the formula I used "Text.BeforeDelimiter(Text.From([Column1]{3}), ",")" which retuns "Error" in the new Column "Period".

The returns says this : Which is strange because it's trying to use the data in Column1 row0 (see screenshot)

Expression.Error: We cannot convert the value "12/6/2023 5:32:31 PM" to type List.
Details:
Value=12/6/2023 5:32:31 PM
Type=[Type]

 

Any help from you pros would be so much appreciated.   Thank you.

 

Screenshots

2 Result of new ColumnDescription1 Adding a new column

 

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi TimTeska,
    you can't call value from another row directly (there are of course ways how to do it), but you can call line from previous step

    Source (previous step):

    Added custom column:

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi TimTeska ,

    Please try:

    Text.BeforeDelimiter(#"Changed Type"{3}[Column1],",")

    Advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9M3MjAyVjA0tzI2UtJRQkGxOtFKjk7OCs75uQWJeZXYpINLk5zz84rzczJTEktSUxSCS3NzE4sqFYJSC/KLShQ0IHRmXromNt0gq+Odc/KLU+MDDXUUnIMNDAyM4oNS0zPz8+K98rwU3B0dAzA0xgIA", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Period", each Text.BeforeDelimiter(#"Changed Type"{3}[Column1],","))
in
    #"Added Custom"

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi TimTeska,
    you can't call value from another row directly (there are of course ways how to do it), but you can call line from previous step

    Source (previous step):

    Added custom column:

    • TimTeska's avatar
      TimTeska
      Frequent Visitor
      2 years ago

      Hi,

      Thanks so much for the help.  I'm still getting an error though.

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        TimTeska that's because you have to refer your prev. step which is #"Changed Type" and not Source. Source was prev. step in my case. See my post again.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi TimTeska ,

    Please try:

    Text.BeforeDelimiter(#"Changed Type"{3}[Column1],",")

    Advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9M3MjAyVjA0tzI2UtJRQkGxOtFKjk7OCs75uQWJeZXYpINLk5zz84rzczJTEktSUxSCS3NzE4sqFYJSC/KLShQ0IHRmXromNt0gq+Odc/KLU+MDDXUUnIMNDAyM4oNS0zPz8+K98rwU3B0dAzA0xgIA", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Period", each Text.BeforeDelimiter(#"Changed Type"{3}[Column1],","))
in
    #"Added Custom"

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group