cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbalza
Helper III
Helper III

Transform Invoice Data

Hi everyone,

 

So I have an ugly data that needed to be transformed. 

How I can move the invoice items in a separate column with all of its data? Thanks in advance.

rbalza_0-1619071953799.png

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @rbalza,

 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZFva8JADMa/SrjXpSTp9dq8POcGFd0fKkMovqjSF4K10qrgt1/16qashcFCCMlD+PFcLsuUREYrT9l9DQECI2M7zfIzBHSb7Ch9C+narZpqW5Xwkpeb7Rnm9bE5tPIll16mutZTye5UbdYFJIeibL7Vx50BeVKtgAPSGj5GNoVZXt85aZMivtRYfB7gMcpvJImR2CEnx517KkmHFBx2Rxz6uscihkxgx5/J03O3SHqYEqGv8T5MD5KI2FlMi/2jRfNfNodoHHtcrN1F/8KOGf0+XGBEYL54n9rXn3vevijS5kpcfgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, #"Due Date" = _t, Code = _t, Name = _t, #"Invoice Total" = _t, #"Invoice Total (Incl. Tax)" = _t]),
    #"Kept First Rows and Removed Columns" = 
    Table.DemoteHeaders(
        Table.RemoveColumns(
            Table.FirstN(Source,1),
        {"Invoice Total", "Invoice Total (Incl. Tax)"}
        )
    ),
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Modified Code Column" = 
            Table.SelectRows(
                Table.SelectColumns(
                    #"Removed Top Rows", {"Code"}
                    ),
                each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
            ),
    #"Modified Invoice Total Column" = 
    Table.DemoteHeaders(
        Table.SelectRows(
            Table.SelectColumns(
                #"Removed Top Rows", {"Invoice Total"}
            ),
            each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
        )
    ),
    #"Modified Invoice Total (Incl. Tax) Column" = 
    Table.DemoteHeaders(
        Table.SelectRows(
            Table.SelectColumns(
                #"Removed Top Rows", {"Invoice Total (Incl. Tax)"}
            ),
            each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
        )
    ),
    #"Transposed Table - Kept First Rows and Removed Columns" = Table.Transpose(#"Kept First Rows and Removed Columns"),
    #"Transposed Table - Modified Code Column" = Table.Transpose(#"Modified Code Column"),
    #"Transposed Table - Modified Invoice Total Column" = Table.Transpose(#"Modified Invoice Total Column"),
    #"Transposed Table - Modified Invoice Total (Incl. Tax) Column" = Table.Transpose(#"Modified Invoice Total (Incl. Tax) Column"),
    #"Combined Tables" = Table.Combine({#"Transposed Table - Kept First Rows and Removed Columns",#"Transposed Table - Modified Code Column",#"Transposed Table - Modified Invoice Total Column",#"Transposed Table - Modified Invoice Total (Incl. Tax) Column"}),
    #"Transposed Table" = Table.Transpose(#"Combined Tables"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number", Int64.Type}, {"Date", type date}, {"Due Date", type date}, {"Code", type text}, {"Name", type text}, {"Invoice Items", type text}, {"Invoice Total", Int64.Type}, {"Invoice Total (Incl. Tax)", type number}})
in
    #"Changed Type"

invoice.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

9 REPLIES 9
Syndicate_Admin
Administrator
Administrator

Hello @rbalza 

Do you means , you want same data feilds  from this particular column into anothor colunm within same table. Right ?

prashpawar_0-1619077198163.png

 

 

if yes  

Could you please try duplicate colunm option?

 

prashpawar_1-1619077358882.png

 

 

prashpawar
Frequent Visitor

prashpawar_0-1619080364471.png

 

prashpawar
Frequent Visitor

firstly 

IN qurey editor 

after creating duplicate coulmn 

try same to removed dupilcates. by right click on new created column you will get option Remove duplicates .

@prashpawar to make it more understandable, I have a column name "code" and underneath it, is the "invoice items data" that I wanted to extract and create in another column. See the red box. Appreciated the help.

rbalza_0-1619082748175.png

 

 

Icey
Community Support
Community Support

Hi @rbalza,

 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZFva8JADMa/SrjXpSTp9dq8POcGFd0fKkMovqjSF4K10qrgt1/16qashcFCCMlD+PFcLsuUREYrT9l9DQECI2M7zfIzBHSb7Ch9C+narZpqW5Xwkpeb7Rnm9bE5tPIll16mutZTye5UbdYFJIeibL7Vx50BeVKtgAPSGj5GNoVZXt85aZMivtRYfB7gMcpvJImR2CEnx517KkmHFBx2Rxz6uscihkxgx5/J03O3SHqYEqGv8T5MD5KI2FlMi/2jRfNfNodoHHtcrN1F/8KOGf0+XGBEYL54n9rXn3vevijS5kpcfgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, #"Due Date" = _t, Code = _t, Name = _t, #"Invoice Total" = _t, #"Invoice Total (Incl. Tax)" = _t]),
    #"Kept First Rows and Removed Columns" = 
    Table.DemoteHeaders(
        Table.RemoveColumns(
            Table.FirstN(Source,1),
        {"Invoice Total", "Invoice Total (Incl. Tax)"}
        )
    ),
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Modified Code Column" = 
            Table.SelectRows(
                Table.SelectColumns(
                    #"Removed Top Rows", {"Code"}
                    ),
                each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
            ),
    #"Modified Invoice Total Column" = 
    Table.DemoteHeaders(
        Table.SelectRows(
            Table.SelectColumns(
                #"Removed Top Rows", {"Invoice Total"}
            ),
            each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
        )
    ),
    #"Modified Invoice Total (Incl. Tax) Column" = 
    Table.DemoteHeaders(
        Table.SelectRows(
            Table.SelectColumns(
                #"Removed Top Rows", {"Invoice Total (Incl. Tax)"}
            ),
            each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
        )
    ),
    #"Transposed Table - Kept First Rows and Removed Columns" = Table.Transpose(#"Kept First Rows and Removed Columns"),
    #"Transposed Table - Modified Code Column" = Table.Transpose(#"Modified Code Column"),
    #"Transposed Table - Modified Invoice Total Column" = Table.Transpose(#"Modified Invoice Total Column"),
    #"Transposed Table - Modified Invoice Total (Incl. Tax) Column" = Table.Transpose(#"Modified Invoice Total (Incl. Tax) Column"),
    #"Combined Tables" = Table.Combine({#"Transposed Table - Kept First Rows and Removed Columns",#"Transposed Table - Modified Code Column",#"Transposed Table - Modified Invoice Total Column",#"Transposed Table - Modified Invoice Total (Incl. Tax) Column"}),
    #"Transposed Table" = Table.Transpose(#"Combined Tables"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number", Int64.Type}, {"Date", type date}, {"Due Date", type date}, {"Code", type text}, {"Name", type text}, {"Invoice Items", type text}, {"Invoice Total", Int64.Type}, {"Invoice Total (Incl. Tax)", type number}})
in
    #"Changed Type"

invoice.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

Thanks so much for the effort. Really appreciate it.

 

prashpawar
Frequent Visitor

yes you can do this

 

 

prashpawar
Frequent Visitor

Hello @rbalza 

Do you means , you want same data feilds  from this particular column into anothor colunm within same table. Right ?

prashpawar_0-1619077198163.png

 

 

if yes  

Could you please try duplicate colunm option?

 

prashpawar_1-1619077358882.png

 

 

Yes, I can use duplicate column. However, I want the duplicated data from original column to be removed. How can I do it?

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors