Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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?
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.
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.
Thanks so much for the effort. Really appreciate it.
yes you can do this
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?
Yes, I can use duplicate column. However, I want the duplicated data from original column to be removed. How can I do it?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.