Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, Power friends,
I have the following data source in excel format.
A | B |
144987 | #199270 #199271 |
148511 | #344827 #344816 #344838 #344823 |
148511 | #344831 #344825 #344840 #344822 #344815 #344834 #344837 #343949 |
I need to separate all data starting with "#" in column "B" to generate a new table with the relationship between column A and B, such as:
A | B |
144987 | #199270 |
144987 | #199271 |
148511 | #344827 |
148511 | #344816 |
148511 | #344838 |
148511 | #344823 |
And, so on...
Note that there is no fixed number of strings beginning with "#" in column B.
Thanks for your time.
Solved! Go to Solution.
I see, but that is exactly what I did before for you. Check the following 2 screenshots:
The steps Source and Navigation on the right hand site are importing Excel file into Power BI. You also see how the data looks like.
The 3rd step is the split described above. Again, you see the result of this step. And it is what you expect, I hope.
Hi @Anonymous ,
the only thing you have to do is to split the column into rows by your delimiter.
If your delimiter is a new line, use #(cr) or #(lf) or both, it depends on your needs.
Very goog @Nolock . This is the easy part. And how about the new table generation?
Thanks for your time.
I've created an Excel file as you have. With Alt+Enter as a line delimiter in Excel.
And then I used the same code as before but as delimiter I use #(lf) - trial/error, I didn't know if the delimiter is cr or lf or both.
let Source = Excel.Workbook(File.Contents("C:\Users\...\Desktop\Book1.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Sheet2_Sheet, {{"Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2") in #"Split Column by Delimiter"
Excuse me, @Nolock I think I was not clear. I'm able to do the split on the current table, but I need another table to make the relationships work, as in the example above. I'm trying some DAX code to make it (new table) after the split process.
Thank you for your time and knowledge
Hi @Anonymous,
unfortunately, I'm still missing your point.
What do you mean with a relationship? We have just one table with 2 columns and they are related by default.
Do you want to create a copy of a query in PowerQuery?
Yes, I need a new table, like the one from the second example above, in which I repeat the value of column "A" for each content of column "B".
Thanks for your patience
I see, but that is exactly what I did before for you. Check the following 2 screenshots:
The steps Source and Navigation on the right hand site are importing Excel file into Power BI. You also see how the data looks like.
The 3rd step is the split described above. Again, you see the result of this step. And it is what you expect, I hope.
You're welcome
@Nolock . One more information: is it possibile to get the same result using only the menu options (without M code)? How?
Thanks again for your kind support.
Almost everything yes. You can split a column by many delimiters (even custom delimiters) into rows (see screenshot in the earlier post) but I didn't find any option for CR or LF among delimiters. So, I clicked everything in UI and at the end I replaced the delimiter:
Splitter.SplitTextByDelimiter("ANOTHER_DELIMITER", QuoteStyle.None)
with
Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |