Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
You may select Split Column By Delimiter in Query Editor.
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |