Forum Discussion
Transferring a cell value to column
Hi All,
Need help please.
I have this date entry in Cloumn 3 Row 1 that I want to transfer to newly added column as report date on each row.
I will then use Row 2 to be my hea
I get this stock on hand report every month and want to keep appending by report date to master file for tracking stock on hand month by month.
Thanks
Nadeem
I copied your data into a query and came up with one way to do what you are looking for. Please paste this into a blank query (open Advanced Editor and replace the text there with this M code). If you are going to automatically combine multiple files, you could use this in your transform sample file query if you use the Combine & Edit feature of Power BI.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVDBasMwDP0V4dMGHSjOkpKjZzutmWNndhJWshzG6KGHrWNNYZ8/t24OHYHq8p4ePD1JfU/q918oVwLMniwIPx7G0MVGjdvPyMT28PGz+x53+y8yLHqSZ4hJ0JOUIiINbFLY2zEIy2dlQDClN+DXykjg1gigNKv0tT/HLMHuzEtWpIgpXCoodeuUb4B1ljNhHzjT0ohWszjtatASMSsmDCudTrGmcfYVuhpm6r87xwmju2Ihk1WKgVU6BGoBtZPeSzHrLia8fCGufPY6WYYPCLBuxYziM+5HGhGxKJanbGts1WoPBWpI6I3NMZ8wUmW4sy8ta+BJrqXZaGgqD3dNze/JMPwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, #"Data snapshot as at: 02/09/19" = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Data snapshot as at: 02/09/19", type text}, {"Column3", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
basetable = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
ExtractDate = Text.AfterDelimiter(basetable[Column3]{0}, "Data snapshot as at: "),
Custom1 = basetable,
#"Removed Top Rows" = Table.Skip(Custom1,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Pax FGD No", Int64.Type}, {"Cust FG No", type text}, {"Item No", type text}, {"Description", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each ExtractDate),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}})
in
#"Changed Type2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
3 Replies
Data snapshot as at: 02/09/19 Pax FGD No Cust FG No Item No Description 65001 1320002 65001 A'KIN DAILY SHINE COND 225ML 65001 160510V01 FA93003 PURIST AVOCADO-CALENDULA COND 65001 70059 70059000 CONTROX VP 65001 70060 70060000 MACADAMIA OIL COLD PRESSED 65001 70069 70069001 AVOCADO OIL REFINED ORGANIC 65001 70042 70000997 MONOMULS 90L 12 65001 70006 70006006 INCROQUAT BEHENYL TMS (TPC) I copied your data into a query and came up with one way to do what you are looking for. Please paste this into a blank query (open Advanced Editor and replace the text there with this M code). If you are going to automatically combine multiple files, you could use this in your transform sample file query if you use the Combine & Edit feature of Power BI.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVDBasMwDP0V4dMGHSjOkpKjZzutmWNndhJWshzG6KGHrWNNYZ8/t24OHYHq8p4ePD1JfU/q918oVwLMniwIPx7G0MVGjdvPyMT28PGz+x53+y8yLHqSZ4hJ0JOUIiINbFLY2zEIy2dlQDClN+DXykjg1gigNKv0tT/HLMHuzEtWpIgpXCoodeuUb4B1ljNhHzjT0ohWszjtatASMSsmDCudTrGmcfYVuhpm6r87xwmju2Ihk1WKgVU6BGoBtZPeSzHrLia8fCGufPY6WYYPCLBuxYziM+5HGhGxKJanbGts1WoPBWpI6I3NMZ8wUmW4sy8ta+BJrqXZaGgqD3dNze/JMPwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, #"Data snapshot as at: 02/09/19" = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Data snapshot as at: 02/09/19", type text}, {"Column3", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
basetable = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
ExtractDate = Text.AfterDelimiter(basetable[Column3]{0}, "Data snapshot as at: "),
Custom1 = basetable,
#"Removed Top Rows" = Table.Skip(Custom1,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Pax FGD No", Int64.Type}, {"Cust FG No", type text}, {"Item No", type text}, {"Description", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each ExtractDate),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}})
in
#"Changed Type2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi, nadeemrajabali
let Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("ndFda8IwFAbgv3LIzTZweNraSrzLmqphadL1QyZzF2UIE7SVtcJg7L+vVQeNDpTlLi/J+3CSly/il+vdprDIqNit173j1ja3DhkRntc5VEW+rd7LGvIK8noEaC/6SBd9i5Lfs4PD1e9ep5pE+SeMJxxUSToE8XdV3eTduKVEvdx0s0HLL6u3j9W2XpUFMbs9F9Eyai3HRkTb6DRPtY3s5lEo4EzIOSRToQLwteJg224oLxMeuhbOOmmLjBl1EB04LoOLslgkKbCZ9hnX9z6TgeKZZAf1IjhEdKmB7ZNmTkNpytJYP8Msgj/WNYqHp4qHp0rImhlYKBhoIZsBJIcoDpIk4FcrZ7N49OyDDk+1N+Jg3PwQBx1PmBL+lcrAPlUQKR2as2ilw0wmQFGCZf/jxdA7U7xu2CpC+bF+ylgKD8E0UHMJaZjAbRr5d+T79Qc=",BinaryEncoding.Base64),Compression.Deflate))), date = Date.FromText(Text.AfterDelimiter(Source{0}[Column3],": "),"en-US"), result = Table.AddColumn(Table.PromoteHeaders(Table.Skip(Source)), "date", each date, type date) in resultIf the Date is not correctly displayed in your local Date format, please change the 2nd parameter of "Date.FromText"
