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.
how to convert Line Break Values from One Cell to Multiple rows in power bi, i have a imported excel there is an 3 cilumn has line break values first column is has Text, second has date, thisrd has number + hypen
please guide me
Hi,
Describe the question in detail and show the expected result. The link you shared is not working.
Please find the file you shared with me earlier, i have added my data with this but the partition stepis not working.
the file which you share after data cleaning was give me my exact reuslt .
hope this one also
please refer below snap for results.
BI File
Excel Data
how to convert Line Break Values from One Cell to Multiple rows in power bi, i have a imported excel there is an 3 cilumn has line break values first column is has Text, second has date, thisrd has number + hypen
Please find the below onedrive link in which i have shared excel file,
i wish to saperate line break cell and convert into row for below column.
Results should be like below
Sum of Rent = $ 750,562,274
Sum of RENT2 = $ 753,108,337
i wish to convert/split them in to row and the data visuals should be reflact on filter selection of the month on visulisation page. i wish to have a table on visulisation page which include data like in wxcel file but it should be replact or change on the selection of monthand display for that month with upcoming expiring product details .
Please note sum of rent should not be exceed than in excel. i tried to split column and then i noticed that rent value gets multiple according to rows after splitting any colum
Hi,
Someone else will help you with solving this question.
Hi,
I just do not understand what you are trying to ask me? What help are you trying to seek? May be someone else will help you.
You have helped me on this earlier and the result are on 3 page of this conversation.
the same data i just shared with whole columns and wish to be redsign the structure of earlier file. which you shared me on Aug 8, 2019. on same query and shared file with me named Data Cleaning V1.
That has only 7-8 column which i pulled from my report and now i just shared my whole report with you, i was not able write below M-code in advance editor hence i reached back to you for guidance for data cleaning.
where you can add "Partition = Table.Group(#"Removed Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),"
Please refer below result file and conversationan, also please help me on this
Hi,
You have not shared the entire data in the Excel workbook. I still only see 5 columns in the Excel file you shared with me. Share the whole dataset and show me the expected result there.
You are welcome. This may take a lot of time to solve. I do not know which row is the culprit there. Furthermore, i am surprised that my number os higher. I have not performed any calculations on the Rent column. If possible, please let me know which cells should ideally be a blank but is actually filled (due to which my number is greater than yours).
@Amardeep100115 wrote:
For example; if row no 25 split to row and one of that row should have rent value and splitted row should not be have any rent value. if there has TBD then ignore.
So if you just want to assign the rent to the first row and ignore it for any of the split rows then you can just add a conditional column using an "if" expression based on the TypeIndex column from the earlier M query.
See the #"Added Custom" step in the middle of the complete M query below. Then in the last 2 steps I remove the original [Rent] column and rename this new column to [Rent]
let Source = Excel.Workbook(File.Contents("C:\TEMP\Working 1.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"As Of Date", type date}, {"Product ID", type text}, {"TRANSACTION TYPE", type text}, {"TYPE", type text}, {"APPROVAL STATUS", type text}, {"Expiration Date", type any}, {" Target Date", type text}, {"Critical Date", type any}, {"Rent", type number}}), #"Split Column1 by Delimiter" = Table.TransformColumns(#"Changed Type", {{"TYPE", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Split Column2 by Delimiter" = Table.TransformColumns(#"Split Column1 by Delimiter", {{"APPROVAL STATUS", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Split Column3 by Delimiter" = Table.TransformColumns(#"Split Column2 by Delimiter", {{" Target Date", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Convert Column1 to table" = Table.AddColumn(#"Split Column3 by Delimiter", "TypeTable", each Table.FromList([TYPE])), #"Add index to Column1 table" = Table.AddColumn(#"Convert Column1 to table", "TypeIndexed", each Table.AddIndexColumn([TypeTable],"TypeIndex",0)), #"Expanded TypeIndexed" = Table.ExpandTableColumn(#"Add index to Column1 table", "TypeIndexed", {"Column1", "TypeIndex"}, {"TYPE.1", "TypeIndex"}), #"Added Custom" = Table.AddColumn(#"Expanded TypeIndexed", "FirstRowRent", each if [TypeIndex] = 0 then [Rent] else null), #"Lookup ApprovalStatus by index" = Table.AddColumn(#"Added Custom", "ApprovalStatus.1", each [APPROVAL STATUS]{[TypeIndex]}), #"Lookup TargetDate by index" = Table.AddColumn(#"Lookup ApprovalStatus by index", "TargetDate.1", each [#" Target Date"]{[TypeIndex]}), #"Removed Columns" = Table.RemoveColumns(#"Lookup TargetDate by index",{"TypeTable", "TypeIndex", " Target Date", "APPROVAL STATUS", "TYPE", "Rent"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"TYPE.1", "TYPE"}, {"ApprovalStatus.1", "ApprovalStatus"}, {"TargetDate.1", "TargetDate"}, {"FirstRowRent", "Rent"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Rent", type number}}) in #"Changed Type1"
That is actually quite a difficult data format to work with.
Basically it looks like you have the following situation where you have multiple columns where you have nested data split onto multiple lines.
And if I understand you correctly you want to split these embedded lines into discreet rows so that you can work with them in Power BI. So I assume that you want output like the following:
(you have not really provided any information about you issue with double counting rent so I'm ignoring that for the time being as that sounds like a separate issue)
Below is an example Power Query script that takes the sample data in my first screenshot and transforms it into the desired output. I could not see any way of doing this in the user interface. I had to hand edit most of the steps.
The basic steps are as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIsSc1VMIzJA9NGQAGXxJJUkACYNlKK1YlWMoKpM4bJG4PFjWHiJlD9plDaDKbOBGqOKZQ2U4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"Column1", type text}, {"Column2", type text}}), #"Split Column1 by Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Split Column2 by Delimiter" = Table.TransformColumns(#"Split Column1 by Delimiter", {{"Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Convert Column1 to table" = Table.AddColumn(#"Split Column2 by Delimiter", "Column1Table", each Table.FromList([Column1])), #"Add index to Column1 table" = Table.AddColumn(#"Convert Column1 to table", "Column1Indexed", each Table.AddIndexColumn([Column1Table],"Column1Index",0)), #"Expanded Column1Indexed" = Table.ExpandTableColumn(#"Add index to Column1 table", "Column1Indexed", {"Column1", "Column1Index"}, {"Column1.1", "Column1Index"}), #"Lookup Column2 by index" = Table.AddColumn(#"Expanded Column1Indexed", "Custom", each [Column2]{[Column1Index]}), #"Removed Columns" = Table.RemoveColumns(#"Lookup Column2 by index",{"Column1", "Column2", "Column1Table", "Column1Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Column2"}, {"Column1.1", "Column1"}}) in #"Renamed Columns"
Hi @d_gosbell
if you accessed my power bi file then please use below code. or if you accessed excel file then import that file and use M-code .
and see the rent column where rent also multiply as per split row.
link https://1drv.ms/u/s!Ai3rGPgy20kLwTWea9DhobGCfAJU?e=d2aooX
https://1drv.ms/u/s!Ai3rGPgy20kLwTWea9DhobGCfAJU?e=d2aooX
Please advice
let
Source = Excel.Workbook(File.Contents("C:\Users\abhingardeve\Desktop\Working .xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"As Of Date", type date}, {"Product ID", type text}, {"TRANSACTION TYPE", type text}, {"TYPE", type text}, {"APPROVAL STATUS", type text}, {"Expiration Date", type any}, {" Target Date", type any}, {"Critical Date", type any}, {"Rent", type number}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"APPROVAL STATUS", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "APPROVAL STATUS"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"APPROVAL STATUS", type text}, {" Target Date", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{" Target Date", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), " Target Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{" Target Date", type text}}),
#"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"TRANSACTION TYPE", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TRANSACTION TYPE"),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"TRANSACTION TYPE", type text}})
in
#"Changed Type3"
@Amardeep100115 wrote:Hi @d_gosbell
if you accessed my power bi file then please use below code. or if you accessed excel file then import that file and use M-code .
I could not see any of your files. The original Excel said it was too large for OneDrive to display and your pbix file shows as having a million rows with no data (and I can't edit the M code as it looks for a file on your desktop)
This is why I made up my own sample data.
However I went back and I can see the smaller cut down excel file. The issue with the rent is a data modelling issue. You will have to store the rent in a different table to the type, approval status and target date columns as the data is at a different grain. We don't really know much about your data model, but you would need a common column in both tables in order to create a relationship (maybe product id)
The following M code is the begining of a query that breaks your line breaks into separate records.
let Source = Excel.Workbook(File.Contents("C:\TEMP\Working 1.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"As Of Date", type date}, {"Product ID", type text}, {"TRANSACTION TYPE", type text}, {"TYPE", type text}, {"APPROVAL STATUS", type text}, {"Expiration Date", type any}, {" Target Date", type text}, {"Critical Date", type any}, {"Rent", type number}}), #"Split Column1 by Delimiter" = Table.TransformColumns(#"Changed Type", {{"TYPE", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Split Column2 by Delimiter" = Table.TransformColumns(#"Split Column1 by Delimiter", {{"APPROVAL STATUS", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Split Column3 by Delimiter" = Table.TransformColumns(#"Split Column2 by Delimiter", {{" Target Date", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), #"Convert Column1 to table" = Table.AddColumn(#"Split Column3 by Delimiter", "TypeTable", each Table.FromList([TYPE])), #"Add index to Column1 table" = Table.AddColumn(#"Convert Column1 to table", "TypeIndexed", each Table.AddIndexColumn([TypeTable],"TypeIndex",0)), #"Expanded TypeIndexed" = Table.ExpandTableColumn(#"Add index to Column1 table", "TypeIndexed", {"Column1", "TypeIndex"}, {"TYPE.1", "TypeIndex"}), #"Lookup ApprovalStatus by index" = Table.AddColumn(#"Expanded TypeIndexed", "ApprovalStatus.1", each [APPROVAL STATUS]{[TypeIndex]}), #"Lookup TargetDate by index" = Table.AddColumn(#"Lookup ApprovalStatus by index", "TargetDate.1", each [#" Target Date"]{[TypeIndex]}), #"Removed Columns" = Table.RemoveColumns(#"Lookup TargetDate by index",{"TypeTable", "TypeIndex", " Target Date", "APPROVAL STATUS", "TYPE"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"TYPE.1", "TYPE"}, {"ApprovalStatus.1", "ApprovalStatus"}, {"TargetDate.1", "TargetDate"}}) in #"Renamed Columns"
Thanks @d_gosbell for your help
Can i have your working file? and csv file as well? so i can understand and work arround
@Amardeep100115 wrote:Thanks @d_gosbell for your help
Can i have your working file? and csv file as well? so i can understand and work arround
I used your cut down excel file, I don't have any csv.
If you open a new Power BI Desktop file, then click the arrow under "Get Data" and choose "Blank Query", then click on Advanced Editor and paste in the M code I posted previously you have my working file. (you'll obviously have to adust the path to the excel file)
Hi @d_gosbell
I have tried your M Code and it seems awesome and feeling happy but the rent get is not showing exact value which is $ 626296157 not $ 663745855
Please help me with sorting this.
@Amardeep100115 wrote:
...but the rent get is not showing exact value which is $ 626296157 not $ 663745855
Yes, as I mentioned previously, you can't use the rent column from the "split" table as it will double up rents where the lines have been split. The best solution is bring in the rent to a separate table and then create relationships between the split table and the "rent" table, but you would need some form of id or key field to link these two.
You could do a hack like summing distinct values across AsOfDate, ProductID, TransactionType and Rent. But work arounds like this are not as good as correctly modelling your data. It looks to me like the Type column is maybe some sort of history of changes or something, so there is a many to one relationship to the rent records.
Rent2 = SUMX(SUMMARIZE(Sheet1, Sheet1[As Of Date], Sheet1[Product ID], Sheet1[TRANSACTION TYPE], Sheet1[Rent]), [Rent])
Do you mean converting the table a to table b?
table a
column1 | column2 | column3 |
a | 1/1/2019 | sd ed rf |
b | 2/1/2019 | df rf yh |
table b
column1 | column2 | column3 |
a | 1/1/2019 | sd |
a | 1/1/2019 | ed |
a | 1/1/2019 | rf |
b | 2/1/2019 | df |
b | 2/1/2019 | rh |
b | 2/1/2019 | yh |
If so, you could open edit queries, use split column option.
If my understanding is not correct, please show me your data example and expected output.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |