Hi DNA Team,
I run into an issue while splitting the data. I can easily split the data in excel by going to text to column -> Fixed width (adjust the size columns) but I would like to do it in PBI.
When I load the text file to Power Bi I can’t split the text into 22 columns.
let
Source = Csv.Document(File.Contents(“C:\Users\MWojnach\Documents\Project\Completed WIP Extract test.txt”),[Delimiter=":", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#“Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type text}}),
#“Filtered Rows” = Table.SelectRows(#“Changed Type”, each not Text.Contains([Column1], “Total extern jobs”) and not Text.Contains([Column1], “Work in progress”) and not Text.Contains([Column1], “Total”) and not Text.Contains([Column1], “N/L acct”) and not Text.Contains([Column1], “General total”) and not Text.Contains([Column1], “Fin.year”) and not Text.Contains([Column1], “From”)),
#“Removed Duplicates” = Table.Distinct(#“Filtered Rows”),
#“Trimmed Text” = Table.TransformColumns(#“Removed Duplicates”,{{“Column1”, Text.Trim, type text}}),
#“Promoted Headers” = Table.PromoteHeaders(#“Trimmed Text”, [PromoteAllScalars=true]),
#“Changed Type1” = Table.TransformColumnTypes(#“Promoted Headers”,{{“Job no. Cat. T/S Parts Hours Hours am. 3rd-party Other Costs Tot.Costprice Type Invoices Customer Misc. Creat.date Chassisnr SP Description Mil.count Hrs.count Finished Closed S Operator”, type text}})
in
#“Changed Type1”
The loaded data is stored in a single column, I did apply filters to and remove duplicated to minimize the data and keep the data that I need. Then I trimmed the column but I can’t split it into 22 columns.
Even if I don’t promote headers I can’t see an option to split the column with width in Power bi.
textsplit.pbix (1.1 MB)
Could please advise how can I split the data into 22 columns.
Many thanks,
Matty
,
