Xero software

[loretto1665]

Hi

Is anyone uploading transaction reports to power query and cleaning them up to use in power BI?  Trying to get the Audit & Accountacny decription away from the date and have it running down the side ?

Can anyone help me , Please ?

Loretto 

Audit & Accountancy fees
19 Aug 2023	Payable Invoice	Xero - Monthly subscription	RPT402-1	46.96	0.00	46.96	56.35	9.39
19 Sep 2023	Payable Invoice	Xero - Monthly subscription	RPT402-1	46.96	0.00	93.92	56.35	9.39
17 Oct 2023	Payable Invoice	Xero - Monthly subscription	RPT402-1	46.96	0.00	140.88	56.35	9.39
Total Audit & Accountancy fees				140.88	0.00	140.88	169.05	28.17

[Anonymous · Accepted solution]

Hi @loretto1665 

Do you mean you want to split the description and date to two columns?

You can refer to the following solution.

Sample data 

Put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3tNQ3MjAyDkisTEzKSVXwzCvLz0xOjUgtylfQVfDNzyvJyKlUKC5NKk4uyiwoyczPU4rViVayJFOfoYG+oTnJGmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Audit & Accountancy fees" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Audit & Accountancy fees", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Audit & Accountancy fees", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Audit & Accountancy fees.1", "Audit & Accountancy fees.2", "Audit & Accountancy fees.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Audit & Accountancy fees.1", Int64.Type}, {"Audit & Accountancy fees.2", Int64.Type}, {"Audit & Accountancy fees.3", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Audit & Accountancy fees.3", Splitter.SplitTextByPositions({0, 4}, false), {"Audit & Accountancy fees.3.1", "Audit & Accountancy fees.3.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Audit & Accountancy fees.3.1", Int64.Type}, {"Audit & Accountancy fees.3.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Audit & Accountancy fees.1", type text}, {"Audit & Accountancy fees.2", type text}, {"Audit & Accountancy fees.3.1", type text}}, "en-US"),{"Audit & Accountancy fees.1", "Audit & Accountancy fees.2", "Audit & Accountancy fees.3.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})
in
    #"Changed Type3"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @loretto1665 

Do you mean you want to split the description and date to two columns?

You can refer to the following solution.

Sample data 

Put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3tNQ3MjAyDkisTEzKSVXwzCvLz0xOjUgtylfQVfDNzyvJyKlUKC5NKk4uyiwoyczPU4rViVayJFOfoYG+oTnJGmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Audit & Accountancy fees" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Audit & Accountancy fees", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Audit & Accountancy fees", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Audit & Accountancy fees.1", "Audit & Accountancy fees.2", "Audit & Accountancy fees.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Audit & Accountancy fees.1", Int64.Type}, {"Audit & Accountancy fees.2", Int64.Type}, {"Audit & Accountancy fees.3", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Audit & Accountancy fees.3", Splitter.SplitTextByPositions({0, 4}, false), {"Audit & Accountancy fees.3.1", "Audit & Accountancy fees.3.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Audit & Accountancy fees.3.1", Int64.Type}, {"Audit & Accountancy fees.3.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Audit & Accountancy fees.1", type text}, {"Audit & Accountancy fees.2", type text}, {"Audit & Accountancy fees.3.1", type text}}, "en-US"),{"Audit & Accountancy fees.1", "Audit & Accountancy fees.2", "Audit & Accountancy fees.3.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})
in
    #"Changed Type3"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[loretto1665]

Hi

thank you so much for this reply, however, the date format is in US as opposed to Uk.  I've tried to change it but it keeps coming up with error..

L

[Anonymous · Accepted solution]

Hi @loretto1665 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUt9A3MjAyDkisTEzKSVXwzCvLz0xOjUgtylfQVfDNzyvJyKlUKC5NKk4uyiwoyczPU4rVAeuzJEufub6hAckaYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Audit & Accountancy fees" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Audit & Accountancy fees", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Audit & Accountancy fees", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Audit & Accountancy fees.1", "Audit & Accountancy fees.2", "Audit & Accountancy fees.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Audit & Accountancy fees.1", Int64.Type}, {"Audit & Accountancy fees.2", Int64.Type}, {"Audit & Accountancy fees.3", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Audit & Accountancy fees.3", Splitter.SplitTextByPositions({0, 4}, false), {"Audit & Accountancy fees.3.1", "Audit & Accountancy fees.3.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Audit & Accountancy fees.3.1", Int64.Type}, {"Audit & Accountancy fees.3.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Audit & Accountancy fees.1", type text}, {"Audit & Accountancy fees.2", type text}, {"Audit & Accountancy fees.3.1", type text}}, "en-US"),{"Audit & Accountancy fees.1", "Audit & Accountancy fees.2", "Audit & Accountancy fees.3.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Merged Columns", {{"Date", type date}}, "en-GB")
in
    #"Changed Type with Locale"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[loretto1665]

recreditable thank you so much this worked perfectly.

thank you so much for all your help.

Loretto ☘