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 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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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"
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.