power query - transform sample file issue

[AlexReed123]

hi,

I have a spreadsheet where i am aggregating the outputs from x number of CSVs / 'Assets' saved in a set folder directory. 

All CSVs are in the same format / layout so i have a 'Transform sample file' function in power query that i used to tidy up the data and then can reference this in my combined dataset / data table in power query.

This all work fine except for one issue:

In the 'transform sample file' query, I have a title in one particular column (Called 'Scenario') that I fill down so that the entire column has the same text string for that particular 'Asset' (In the case of the 'transform sample file' query, it is the first 'Asset' / CSV in the folder directory. At this stage it looks good and how i woud like it to look in the combined dataset (i.e the dataset of all of the assets kind of stacked on top of eachother).

However in my combined dataset, when I 'Expand transform file', all of the rest of the combined CSV data is pulled through correctly (Kind of stacked on top of eachother), however the text string in the 'Scenario' column now shows null for all 'Assets' / CSVs.

Can sopmebody please help with this issue

Thanks

[Jimmy801]

Hello @AlexReed123 

it's very hard to understand what exactly is going on, but I suppose that the column "scenario" is the starting column4. And with this step you are basically emptying this column

#"Replaced Value1" = Table.ReplaceValue(#"Added Index",each if [Index] <> 1 then [Column4] else null, null, Replacer.ReplaceValue,{"Column4"}),

So how you can expect that this column is containing data?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AlexReed123]

The below is the code for the transform sample file.

Note that the structure of the 'scenario' column is that i want the first item in that column. So i add an index column and then say that i want to only keep the item in the scenario column where the index colunm is 1 (Probably a better way to do that). 

let
Source = Csv.Document(#"Sample File Parameter1",[Delimiter=",", Columns=50, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Removed Columns" = Table.RemoveColumns(Source,{"Column3", "Column5", "Column6", "Column7", "Column8", "Column9"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
#"Replaced Value1" = Table.ReplaceValue(#"Added Index",each if [Index] <> 1 then [Column4] else null, null, Replacer.ReplaceValue,{"Column4"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value1",{"Index"}),
#"Filled Down" = Table.FillDown(#"Removed Columns1",{"Column4"}),
#"Replaced Value3" = Table.ReplaceValue(#"Filled Down","",null,Replacer.ReplaceValue,{"Column1"}),
#"Filled Down1" = Table.FillDown(#"Replaced Value3",{"Column1"}),
#"Replaced Value4" = Table.ReplaceValue(#"Filled Down1","",null,Replacer.ReplaceValue,{"Column2"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value4", each ([Column2] <> null and [Column2] <> "Period counter (cumulative)" and [Column2] <> "Period counter (in year)" and [Column2] <> "Period type" and [Column2] <> "Summary period counter")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
#"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers1",{Table.ColumnNames(#"Promoted Headers1"){2}, "Scenario"}),
#"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{Table.ColumnNames(#"Renamed Columns1"){0}, "Line Item Type"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns2", {"Line Item Type", "Period end date", "Scenario"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{Table.ColumnNames(#"Unpivoted Other Columns"){0}, "Line Item Type"},{Table.ColumnNames(#"Unpivoted Other Columns"){1}, "Line Item"},{Table.ColumnNames(#"Unpivoted Other Columns"){2}, "Scenario"},{Table.ColumnNames(#"Unpivoted Other Columns"){3}, "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", Int64.Type}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Errors",{{"Value", type number}}),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Value"})
in
#"Removed Errors1"

The below is the code to aggregate and perform the transform sample file:

let
Source = Folder.Files(CSVLocation),
#"Filtered Out Sub folders" = Table.SelectRows(Source, each ([Folder Path] = CSVLocationB)),
#"Filtered for CSVs only" = Table.SelectRows(#"Filtered Out Sub folders", each ([Extension] = ".csv")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered for CSVs only",{"Content", "Name", "Date accessed", "Date modified", "Date created", "Attributes"}),
#"Filtered Hidden Files Out" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function" = Table.AddColumn(#"Filtered Hidden Files Out", "Transform File", each #"Transform File"([Content])),

[AlexReed123]

Thanks for getting back to me.

They are all identical in structure so yes they all have the same column

[wdx223_Daniel]

@AlexReed123 provide sample data or M code is the faster way to help you

[edhans]

Do all of your other files @AlexReed123 have this same column? It seems like they do not, so when they get combined into a single table at the end, missing columns get filled with nulls.