Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jh_dempsey
Frequent Visitor

Produce a table on the fly from CSV data stored in a field from another table

Hi All

 

I have a table of all the concrete water channels which are to be constructed on our project.

One of the columns in the table is called "Invert Levels", and this contains a list of values all separated with a comma (ie a CSV file)

Each value is actually made up of two values. The first value is a distance, and the second value is the invert level at that distance.

The two values are separated by a colon (ie distance : invert level). An example of one these strings is below

 

0:-0.0925,1:-0.0728151602407321,2:-0.0531303204814642,3:-0.0334454807221963,4:-0.0137606409629284,5:0.00592419879633947,6:0.0256090385556074

 

What i want is for a user to be able to select one of the records in the table. Power BI then produces a table on the fly by reading the values stored within this CSV string. Using the example string above, i want a table producing that looks like this:

 

Distance   Invert Level

0               -0.0925,

1               -0.0728151602407321,

2               -0.0531303204814642,

3               -0.0334454807221963,

4               -0.0137606409629284,

5                0.00592419879633947,

6                0.0256090385556074

 

Is this possible within Power BI? Can anyone give me some pointers in the right direction? Being fairly new to Power BI i dont know where to start with this!

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@jh_dempsey,

 

You may select Split Column By Delimiter in Query Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks v-chuncz-msft, although im not sure if its the ideal solution for what i need.

Eventually, what i want is for a user to see a table of all the surface water channels in the scheme. I then want them to be ablt to click on a record (or select from a drop down menu, or any way of selecting a single record) and then get taken to a page which reads the csv string of that row, and produces a table on the fly which lists the distances and invert levels.

 

Ive got a feeling i need to do this via Power Query / M Query

 

I made a start on the code as you can see below. It takes the table containing all the surface water channels (500DGCH) and gets the csv value of the chosen record, which at the moment is specified by the RowNum varaible.

 

The rest of the code then processes that CSV and takes the first value, splits that value into the distance and invert level values (splitting at the : symbol) and puts it into a table ive created at the very start of the code.

At the moment, this only works for the first value in the CSV because ive hard coded it take list1{0}

 

I now need some way of looping through all the items in the list, and adding the row to the table. From various readin, it looks like List.Accumulate is the way to go, but im struggling to understand it and make it work. Anyone able to help out?

 

 

let

    SettingOut = #table(
    {"Chainage","Invert Level"},
    {
        
    }
    ),

    RowNum = 1,
    Source = #"500DGCH",
    csv = Source[what3words]{RowNum},
    
    /* Example csv value:
    0:-0.0925,1:-0.0728151602407321,2:-0.0531303204814642,3:-0.0334454807221963,4:-0.0137606409629284,5:0.00592419879633947
    */

    list1 = Text.Split(csv,","),

    /* list1 contains hundreds of values. The below code inserts the first item in this list into the table. I now need
    something which will repeat this for all the items in the list */

    values = Text.Split(list1{0},":"),

    SettingOutAdd = Table.InsertRows(SettingOut,0,{[#"Chainage" = values{0}, #"Invert Level" = values{1}]})

in
    SettingOutAdd

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.