Split string when there is no delimiter that I can see - only spaces

Power Query
1

Hi

This one might be for @Melissa but I’m not sure. Hope someone can help me out.

I have a string that I have copied from an open website https://cefanalyzer.com/app/screener - it generates daily lists of net asset values by ticker. That’s what I want - I will then add that to my report which already has many other variables and stats in Power BI.

My problem is that it’s one string with only spaces. I can get one column but then what do I do? I know it’s about transposing and creating lists but that’s as far as I’ve got. In other words, I haven’t really a clue how to tackle this
I’m attaching the file. There’s nothing confidential about the data.

net asset values.xlsx (21.4 KB)

Any help would be greatly appreciated.

2

@Ericadyson Not sure what you did, but when I import it using Web connector I get this table.

image
image1911×764 62.7 KB

3

How bizarre. I didn’t use BI - I was in Excel - Get Date - From Web - pasted in the url but didn’t get the data. Why is that do you think?

Mega thanks, of course. But why didn’t I get the same???

4

@Ericadyson No idea why but when you use Web connector in Excel it is using a different function, the fucntion that PBI is using isn’t availale in Excel.

Excel’s Power Query and PowerPivot are really outdated compared to PowerBI considering Excel is Microsoft’s most popular tool :man_facepalming:

1 Like
5

Hello @Ericadyson, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi. Apologies for not replying… my bad. Everything’s fine. Actually the way the data came in wasn’t so good (seemed to be 2 columns of tickers in one table) and also it didn’t seem reliable data so I abandoned the task. But realizing that Excel PQ and Power BI are not the same was illuminating. Thanks a lot for clarifying that and trying to help me out. Regards

7

Actually so BTW, if I get 2 columns that should be one column, how would I do that. ie Column A has 100 rows but column D has another 100 entries but they should be rows, so the query should have 200 rows not 100.

8

Hi @Ericadyson,

You could do something like this:
In this example I’m splitting my table in two, rename the columns so the column names match in both tables before combining them.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgRrBjdXDKxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t, ColumnD = _t, ColumnE = _t, ColumnF = _t]),
    FirstTable = Table.SelectColumns(Source,{"ColumnA", "ColumnB", "ColumnC"}),
    SecTable = Table.SelectColumns(Source,{"ColumnD", "ColumnE", "ColumnF"}),
    RenameColsSecTable = Table.RenameColumns(SecTable, List.Zip( { Table.ColumnNames( SecTable), Table.ColumnNames( FirstTable)})),
    CombineFirstAndSec = Table.Combine( { FirstTable, RenameColsSecTable } )
in
    CombineFirstAndSec

I hope this is helpful

9

Hi Melissa
Nice to hear from you! Actually, after I wrote my cry for help, I thought… hmm…why don’t I duplicate the table, rename the columns so they are the same and then append. And that seemed to do the trick! So I even managed to solve my own problem… but I’m going to keep your code because I’m sure it’s better than what I did. Thanks a lot for reaching out and helping (as always).

1 Like