how to convert Line Break Values from One Cell to Multiple/next rows in power bi

[Amardeep100115]

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 

[Ashish_Mathur]

Hi,

Describe the question in detail and show the expected result.  The link you shared is not working.

[Amardeep100115]

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

Click Here 

Excel Data

Click Here 

[Amardeep100115]

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

Excel Data 

[Ashish_Mathur]

Hi,

Someone else will help you with solving this question.

[Ashish_Mathur]

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.

[Amardeep100115]

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 

Conversation Click here  

[Ashish_Mathur]

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.

[Ashish_Mathur]

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).

[d_gosbell]

---

@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"

[d_gosbell]

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:

• convert all the columns with embedded lines into embedded lists by splitting on linefeed characters
• then converted one of the columns from embedded lists to embedded tables
• add a second index column to the column with embedded tables
• then expand the column with embedded tables into rows
• then use the index column to lookup entries from the other list column(s)
• then I removed all the temporary working columns that were not needed for the final output

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"

[Amardeep100115]

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"

[d_gosbell]

---

@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"

[Amardeep100115]

Thanks @d_gosbell  for your help

Can i have your working file? and csv file as well?  so i can understand and work arround

[d_gosbell]

---

@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)

[Amardeep100115]

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. 

[d_gosbell]

---

@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])

[v-juanli-msft]

Hi @Amardeep100115 

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.

https://support.office.com/en-gb/article/split-a-column-of-text-power-query-5282d425-6dd0-46ca-95bf-...

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.