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 2023Payable InvoiceXero - Monthly subscriptionRPT402-146.960.0046.9656.359.3919 Sep 2023Payable InvoiceXero - Monthly subscriptionRPT402-146.960.0093.9256.359.3917 Oct 2023Payable InvoiceXero - Monthly subscriptionRPT402-146.960.00140.8856.359.39Total Audit & Accountancy fees 140.880.00140.88169.0528.17

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.

Forum Discussion

loretto1665

Frequent Visitor

2 years ago

Solved

Xero software

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

Need Help

Anonymous

2 years ago

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.

Xero software.pbix37 KB

Anonymous

2 years ago

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.

4 Replies

Anonymous

Not applicable

2 years ago

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.

Xero software.pbix37 KB

loretto1665

Frequent Visitor

2 years ago

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

Anonymous

Not applicable

2 years ago

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.

Forum Discussion

Xero software

4 Replies

Related content

XERO Connection

Forecast Using Multiple Models by MAQ Software

ERROR: Calendar Visual by MAQ Software showing error message

Xero

Xero Connection