Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Marky_Mark
Regular Visitor

Combining Workbook Sheets from Folder: Transform Sample File Problem.

Hi.

 

I'm trying to use Power Query to chop up a monthly report that contains profit and loss information. It contains effectively sections of cross tabulation, which I am trying to put together and then unpivot to get a nice denormalised data set. It should do this 12 times for each month report using the combine function in order to produce one year's worth of data for the financial year.

 

I can't provide any data or the workbook since it is work related, but here is what I can share. So, any help with this would be appreciated. 

 

I have chopped up the report in the transformation sample file query. However, it does not seem to use it when power query combines the data across each of the workbooks in the folder. Instead it just tries to combine the data from each workbook as it is at source.

 

Marky_Mark_0-1651217239988.png

Here's the m code from advanced editor:

 

Transform Sample File Query:

 

let
Source = Table.Combine({#"Transform Sample File - Column Header", #"Transform Sample File - Row Values"}),
#"Removed Columns" = Table.RemoveColumns(Source,{"Gross Profit Project Comparison Report", "Column2", "Column4", "Column6"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","N/A","Subcategory",Replacer.ReplaceText,{"Remapped Category"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Remapped Category", "Column8", "Column10", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","",0,Replacer.ReplaceValue,{"Remapped Category", "Column8", "Column10", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value2"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}})
in
#"Changed Type"

 

Combine Report Query:

 

let
Source = Folder.Files("C:\Users\****.*****\Desktop\New folder"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"})
in
#"Removed Other Columns1"

 

Thanks,

 

M

4 REPLIES 4
KT_Bsmart2gethe
Impactful Individual
Impactful Individual

Hi @Marky_Mark 

 

Do you have the code below that you can share?

Source = Table.Combine({#"Transform Sample File - Column Header", #"Transform Sample File - Row Values"})

 

I am trying to understand where which part is not flowing.

 

Below file I created similar to your case and hopefully it helps.

https://docs.google.com/spreadsheets/d/10TP8s0qyFzs7PNFxuQ7yaDwV7_ae3bJd/edit?usp=sharing&ouid=11302...

 

This is the video showing how it created.

 

Regards

KT

Transform Sample File - Column Header:

 

let
Source = Excel.Workbook(Parameter1, null, true),
#"Monthly PPM-5029-Gross Profit P_Sheet" = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Monthly PPM-5029-Gross Profit P_Sheet", [PromoteAllScalars=true]),
#"Removed Top Rows" = Table.Skip(#"Promoted Headers",6),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",13),
#"Remove Empty Columns" = FnRemoveEmptyColumns(#"Removed Bottom Rows"),
#"Reordered Columns" = Table.ReorderColumns(#"Remove Empty Columns",{"Gross Profit Project Comparison Report", "Column2", "Column4", "Column6", "Column8", "Column10", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Gross Profit Project Comparison Report] <> "" and [Gross Profit Project Comparison Report] <> " ")),
#"Get Last Column Position" = {List.Last(Table.ColumnNames(#"Filtered Rows"))},
#"Remove Last Column" = Table.RemoveColumns(#"Filtered Rows", #"Get Last Column Position"),
#"Replace Category Null Values" = Table.ReplaceValue(#"Remove Last Column",null,"N/A",Replacer.ReplaceValue,{"Gross Profit Project Comparison Report", "Column4", "Column6"}),
#"Added Conditional Column" = Table.AddColumn(#"Replace Category Null Values", "Remapped Category", each if Text.Contains([Column6], "Labour - ") then "Payroll Costs" else if Text.Contains([Column6], "Vehicle") then "Vehicle" else if Text.Contains([Gross Profit Project Comparison Report], "Gross Profit") then "Gross Profit" else if Text.Contains([Gross Profit Project Comparison Report], "Gross Margin") then "Gross Margin" else [Column6]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Gross Profit Project Comparison Report", "Column2", "Column4", "Column6", "Remapped Category", "Column8",
"Column10", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108"}),
#"Kept First Rows" = Table.FirstN(#"Reordered Columns1",1)
in
#"Kept First Rows"

 

Transform Sample File - Row Values:

 

let
Source = Excel.Workbook(Parameter1, null, true),
#"Monthly PPM-5029-Gross Profit P_Sheet" = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Monthly PPM-5029-Gross Profit P_Sheet", [PromoteAllScalars=true]),
#"Removed Top Rows" = Table.Skip(#"Promoted Headers",6),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",13),
#"Remove Empty Columns" = FnRemoveEmptyColumns(#"Removed Bottom Rows"),
#"Reordered Columns" = Table.ReorderColumns(#"Remove Empty Columns",{"Gross Profit Project Comparison Report", "Column2", "Column4", "Column6", "Column8", "Column10", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Gross Profit Project Comparison Report] <> "" and [Gross Profit Project Comparison Report] <> " ")),
#"Get Last Column Position" = {List.Last(Table.ColumnNames(#"Filtered Rows"))},
#"Remove Last Column" = Table.RemoveColumns(#"Filtered Rows", #"Get Last Column Position"),
#"Replace Category Null Values" = Table.ReplaceValue(#"Remove Last Column",null,"N/A",Replacer.ReplaceValue,{"Gross Profit Project Comparison Report", "Column4", "Column6"}),
#"Filter out Category Null Values" = Table.SelectRows(#"Replace Category Null Values", each [Column6] <> "N/A" and [Column4] <> "N/A"),
#"Added Conditional Column" = Table.AddColumn(#"Filter out Category Null Values", "Remapped Category", each if Text.Contains([Column6], "Labour - ") then "Payroll Costs" else if Text.Contains([Gross Profit Project Comparison Report], "Gross Profit") then [Gross Profit Project Comparison Report] else if Text.Contains([Gross Profit Project Comparison Report], "Gross Margin") then [Gross Profit Project Comparison Report] else if [Column2] = "Timesheets" then [Column2] else if [Column2] = "On-Territory" then [Column2] else if [Column2] = "Non-Pass Through Costs" then [Column2] else if [Column2] = "Pass Through - WIP Release" then [Column2] else if [Column2] = "Revenue" then [Column2] else if [Column2] = "Revenue & Billing" then [Column2] else [Column4]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Conditional Column",{"Gross Profit Project Comparison Report", "Column2", "Column4", "Column6", "Remapped Category", "Column8",
"Column10", "Column12", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108"})
in
#"Reordered Columns1"

Hi,

 

The two sample files for Column headers and Row Values has not created as a function. (i.e. fx .......). These two samples take the Parameter1, which refer to "Monthly PPM-5029-Gross Profit P_Sheet". This mean the transformation you've done are for "Monthly PPM-5029-Gross Profit P_Sheet". In order to apply to apply it to each workbook, it needs to be a function.

Below code posted by you. See orange text below is a function refer to "fx Tranform file" column [Content). It applies the transformation you did to each workbook/file you importing. It needs to something like "#"Remove Empty Columns" = FnRemoveEmptyColumns(#"Removed Bottom Rows")"

 

Combine Report Query:

 

let
Source = Folder.Files("C:\Users\****.*****\Desktop\New folder"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"})
in
#"Removed Other Columns1"

 

I just realised I didn't attached the video link above but the file.

Watch the below video see if it can helps you. 

https://youtu.be/K9IB9bLxEXo

 

File for above video:

https://docs.google.com/spreadsheets/d/1liW9KEoO8fVRohG6PveYdLObokAmxILz/edit?usp=sharing&ouid=11302...

 

Regards

KT

 

 

KT_Bsmart2gethe
Impactful Individual
Impactful Individual

Hi,

 

I suggest to transform the data in "Combine Report" query. The query you transformed is a sample which is not a function, the M code need to be written in "fx Transform file" not "Transform Sample File".

 

1. You can copy the above code and paste it carefully into "fx Transform file". If your monthly data come in inconsistently format, your query will return error.

 

I will write something later based on your code above and shared it back to you later.

 

Regards

KT

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors