Transform Invoice Data

[rbalza]

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.

[Syndicate_Admin]

Hello @rbalza 

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

if yes  

Could you please try duplicate colunm option?

[prashpawar]

[prashpawar]

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 .

[rbalza]

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

[Icey · Accepted solution]

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"

Best Regards,

Icey

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

[rbalza]

Thanks so much for the effort. Really appreciate it.

[prashpawar]

yes you can do this

[prashpawar]

Hello @rbalza 

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

if yes  

Could you please try duplicate colunm option?

[rbalza]

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