Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Sep 20, 2024
Solved

Power Query or any other solution

Dear Experts,                       I have a data in the below format(attached txt file too). Each Packet( example for Packet-1) has the same structure. I want to export , it to excel , with...
BI & Data Analysis
excel
Formulas and Functions
power bi
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Sep 22, 2024

    anupambit1797 In case you are still interested in a PQ solution, the attached file contains a pretty straight-forward one. Just change the source path to the correct location on your own system and see it it produces the correct results.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 22, 2024

anupambit1797 In case you are still interested in a PQ solution, the attached file contains a pretty straight-forward one. Just change the source path to the correct location on your own system and see it it produces the correct results.

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    Sep 27, 2024

    Thanks Riny_van_Eekelen , If I may ask on the same question:-

     

    Q1) How to add a Column for the corresponding Length for each line?

    I tried to do the same , but it fetch me an Err as below

     

    Q2) Secondly , if I want to add a Column with the Number of (Count of ) "|" in each string/line how to achieve that?

     

    Feel free to use the same sample txt file for the above exercise..

    Many Thanks in Advance,

    Br,

    Anupam

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Sep 28, 2024

      anupambit1797 

      Regarding 1, I did this:

       

      = Table.AddColumn(Source, "Length", each Text.Length([Column1]), Int64.Type)

       

      It seems you are missing some parentheses.

       

      Regarding 2, I did this:

       

      = Table.AddColumn(Source, "Custom", each Text.Length (Text.Select ([Column1], "|")))

       

       

Resources