Forum Discussion
Save data for use later
I have a report that's gets exported to excel but contains a lot of information I don't need. One piece of info that's tucked in the section I don't need is the report run date. I need to save this for use later down in the process, after I clean up the data.
Here's a example of what I mean...
The data in black is info I don't need. The data in blue is the info I do need. The info in red is explained below.
I need to retain the report date so i can extract the report month. That field will then be used as part of a join so I can pull data corresponding to that month in another table.
The first thing I did was create a custom column with a formula that extracted the report date from the first column:
if Text.Contains([Data.Column1],"Data As Of:") then Text.Start(Text.AfterDelimiter([Data.Column1], "Data As Of: "),10) else null
Next, I did a fill down to populate the rest of the columns and then created a custom column named "Report Month" to extract the month number only.
The issue I'm having is that the data i need doesn't start until row 7. To get to this, I excluded anything in Column 2 that is null. In doing so, however, I lost the column header for the Report Month. So when I promote headers, my header is now "8"... which obviously isn't going to work for my join as the report month will change with each update.
How do I get around this?
I'd suggest doing this with independent steps.
Try pasting this into your Advanced Editor and looking at the applied steps one at a time.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5PC4JAEMW/yrBnwXb7Q3hTO0ZleUjEw2CTBObIth789k0qiBDMwPzmvTdMnqsrtWwdnPBNEEax8tRUhZerG3e2pAC0WXtwQIeAH+BnAHtfa9+sjF76l5RoSNlhPa4SM9NPlZujsNnu/udjdFSx7YWPXKJ7cSNj1D0qcpARWqG0b2kwhwJ36empc0tWEk01iJFsslm8YG+5rlVRfAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), DateCellText = Table.SelectRows(Source, each Text.Contains([Column1], "Data as of"))[Column1]{0}, AsOfDate = Text.AfterDelimiter(DateCellText, "Data as of:"), #"Removed Top Rows" = Table.Skip(Source,6), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Location", type text}, {"Budget Year", Int64.Type}, {"Type", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "ReportDate", each Date.FromText(AsOfDate), type date) in #"Added Custom"
2 Replies
I'd suggest doing this with independent steps.
Try pasting this into your Advanced Editor and looking at the applied steps one at a time.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5PC4JAEMW/yrBnwXb7Q3hTO0ZleUjEw2CTBObIth789k0qiBDMwPzmvTdMnqsrtWwdnPBNEEax8tRUhZerG3e2pAC0WXtwQIeAH+BnAHtfa9+sjF76l5RoSNlhPa4SM9NPlZujsNnu/udjdFSx7YWPXKJ7cSNj1D0qcpARWqG0b2kwhwJ36empc0tWEk01iJFsslm8YG+5rlVRfAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), DateCellText = Table.SelectRows(Source, each Text.Contains([Column1], "Data as of"))[Column1]{0}, AsOfDate = Text.AfterDelimiter(DateCellText, "Data as of:"), #"Removed Top Rows" = Table.Skip(Source,6), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Location", type text}, {"Budget Year", Int64.Type}, {"Type", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "ReportDate", each Date.FromText(AsOfDate), type date) in #"Added Custom"Thanks - with some tweaking I was able to get this to work!
