Forum Discussion
Power BI - question
I have a set of data which get converted as per my below query. but few lines which i want it to be text but get converted to date format, below is the screenshot. i dont want this to be converted.
let
Source = Excel.Workbook(File.Contents("C:\Users\\ General\Training\Power Query samples\New folder\PQ MASTER FILE.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Ad_Transformed = Table.AddColumn(Sheet1_Sheet, "Transformed", each
[ numbers = {"0".."9"},
alphabets = {"a".."z", "A".."Z"},
a = Text.ToList([Column3]),
b = List.Select(a, each not List.Contains(alphabets & numbers, _)), //Characters to remove/replace
c = Text.Combine(List.Transform(a, each if List.Contains(b, _) then "-" else _)), //replace other special characters with "-"
d = Text.Combine(List.RemoveItems(Splitter.SplitTextByDelimiter("-")(c), {""}), "-"), //remove extra dashes
e = Splitter.SplitTextByCharacterTransition(each true, (x)=> List.Contains(alphabets & numbers, x))(d), //split any|text
f = Text.Combine(List.Transform(e, each if List.Contains(alphabets, _, (x,y)=> Text.StartsWith(y,x)) then Text.Start(_,1) else _)),
g = Splitter.SplitTextByCharacterTransition((x)=> List.Contains(alphabets & numbers, x), each true)(f), //split text|any
h = Text.Combine(List.Transform(g, each if List.Contains(alphabets, _, (x,y)=> Text.EndsWith(y,x)) then Text.End(_,1) else _))
][h], type text),
#"Promoted Headers" = Table.PromoteHeaders(Ad_Transformed, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Cage code", type text}, {"Manufacturer Name", type text}, {"Part Number", type text}, {"Item Description", type text}, {"Net price", type number}, {"Unit of Measure", type text}, {"Currency", type text}, {"Lead Time", Int64.Type}, {"Standard Package Qty", Int64.Type}, {"Minimum Order Qty", Int64.Type}, {"Catalog Year", Int64.Type}, {"PartNumber", type text}})
in
#"Changed Type"
Hi aqeel_shaikh ,
Thanks for the reply, follow your steps in my visualization to export data as .csv files. The problem you described does arise, which may be due to the different methods of Excel and Power BI for data type recognition. As an alternative, you can right-click on the converted column to check the copy option and paste it into Excel, which will allow you to achieve the same format as in Power BI.Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi aqeel_shaikh ,
Based on your description, I took a screenshot of some of your data for testing. The first thing to be clear about is that in Power BI, the format of a column is uniform across the entire column. So only the text format will appear. Here's a screenshot of my testlet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcqxCcAwDATAXVTn4V8yROq9hdH+azixy4Nby1STLwsM6+cnPji9LjGSoQQvFcwBHdBPldK6Nw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column3 = _t]), Ad_Transformed = Table.AddColumn(Source, "Transformed", each [ numbers = {"0".."9"}, alphabets = {"a".."z", "A".."Z"}, a = Text.ToList([Column3]), b = List.Select(a, each not List.Contains(alphabets & numbers, _)), //Characters to remove/replace c = Text.Combine(List.Transform(a, each if List.Contains(b, _) then "-" else _)), //replace other special characters with "-" d = Text.Combine(List.RemoveItems(Splitter.SplitTextByDelimiter("-")(c), {""}), "-"), //remove extra dashes e = Splitter.SplitTextByCharacterTransition(each true, (x)=> List.Contains(alphabets & numbers, x))(d), //split any|text f = Text.Combine(List.Transform(e, each if List.Contains(alphabets, _, (x,y)=> Text.StartsWith(y,x)) then Text.Start(_,1) else _)), g = Splitter.SplitTextByCharacterTransition((x)=> List.Contains(alphabets & numbers, x), each true)(f), //split text|any h = Text.Combine(List.Transform(g, each if List.Contains(alphabets, _, (x,y)=> Text.EndsWith(y,x)) then Text.End(_,1) else _)) ][h], type text) in Ad_TransformedPlease provide a screenshot of your code after it runs in the data and your desired result. This way we can help you faster. Please redact sensitive information in advance.
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Albert, i forgot to mention that when i download to Excel, specific data converted to date format.
can you please try exporting the data you demonstrated from Power BI to Excel and see if you get the same result.
Hi aqeel_shaikh ,
Thanks for the reply, follow your steps in my visualization to export data as .csv files. The problem you described does arise, which may be due to the different methods of Excel and Power BI for data type recognition. As an alternative, you can right-click on the converted column to check the copy option and paste it into Excel, which will allow you to achieve the same format as in Power BI.Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
