Transferring a cell value to column

[nadeemrajabali]

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

[ziying35]

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
    result

If the Date is not correctly displayed in your local Date format, please change the 2nd parameter of "Date.FromText"

[nadeemrajabali]

		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)

[mahoneypat · Accepted solution]

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