CSV Data Issue

[donodackal]

Hello

I created a query that combines multiple CSV files from a SharePoint folder. All the CSV files have the same format where the header row data is a few rows below. On applying the query steps, I have deleted the first few rows and the relevant row is promoted to the header. My understanding is the same steps is applied to all files from the first file. Copied below is how the raw file looks and what I have deleted/promoted:

Rows I deleted	Report Name	Supplier Opportunities (by Supplier List)
Description	Shows RFx's offered to supplier and responses (only data entered after 1st March 2012 shown)
Date Range (GMT)	From 01/Mar/2012 To 25/Jan/2022
Supplier list selected	Data
Row I promoted to the header	Supplier Business Name	Active On List	Business Number Type	Business Number	Rfx Name	RFx Reference
Data	Data	Data	Data	Data	Data	Data
Data	Data	Data	Data	Data	Data
Data	Data	Data	Data	Data	Data
Data	Data	Data	Data	Data	Data

In the final output, the data is all fine but a few rows that I either deleted or promoted to as header is appearing in the data. See below:

Header >>	Source file Name	Supplier Business Name	Active On List
Data>>	Source file Name	Supplier list selected	Creative Services Panel
Source file Name	Supplier Business Name	Active On List
Source file Name	Supplier list selected	Training Services Panel
Source file Name	Supplier Business Name	Active On List
Source file Name	Supplier list selected	Employees Assistance Program Panel
Source file Name	Supplier Business Name	Active On List
Source file Name	Supplier list selected	Medical Services Scheme
Source file Name	Supplier Business Name	Active On List
Source file Name	Supplier list selected	Territory Valuation Services Scheme

Any help is appreciated. 

[v-shex-msft]

Hi @donodackal,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[sevenhills]

I look your issue as dynamic row number for the header row. If you want to remove up to header rows of each file and later promote the column headers. There are multiple ways to solve it. 

Before you try my steps, Take a backup of your .pbix file.

Power BI creates the Transform file function. we are going to edit this function for your needs

(fx) -- Transform file

let
    Source = (Parameter1 as binary) => let
        Source = Csv.Document(Parameter1,[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
        #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if [Column1] = "Region" then [Index] - 1 else null, Int32.Type),
        #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Index] > [Custom] or [Custom] is null)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Index"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    Source

You need to changes these lines as per your file content

a) [Column1] = "Region" ... replace "Region" with the text on column1, to identify the Data table header
b) Remove "Source.Name" in the query/table step that calls the transform file function

[VijayP]

@donodackal 

In Power Query what ever steps applied in first file ( in case of Folder with CSV Files), that will automatically takes care of other files. But in your case that is not happeing!

If you can share some sample data without classified information. I can help you!