Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have attached a snapshot of the table I have it’s two images but in reality, it’s one table.
It’s set up in excel sheet in this way and I do not want to change it in excel sheet rather than that I want to do the changes in power bi
I want to assign certain rows as column headers as I have highlighted in yellow colors To be something like this:
DS | SOR for Drilling | SOR for Well Services | LRC Issued for Pad | Civils Complete for Drilling | Rig Spud | Rig Release | Etc… | POP | Comments | Etc… |
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 | Data | Data | Data | Data | Data | Data | Data | Data |
ps: you can download the sample PBIX file from PBIX File
Solved! Go to Solution.
This is much more difficult than it should be because of how the file is laid out, but I get your users are comfortable with that structure. A true Excel Table would make this a non-issue.
Paste this code into a blank query in Power Query.
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive - eHansalytics\Downloads\File2.xlsx"), null, true),
#"MP Date_Sheet" = Source{[Item="MP Date",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"MP Date_Sheet",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}}),
#"Kept First Rows" = Table.FirstN(#"Changed Type",5),
#"Filled Down" = Table.FillDown(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36"}),
#"New Header" = Table.LastN(#"Filled Down", 1),
#"Data Rows" = Table.Skip(#"Changed Type", 5),
#"New Table" = Table.Combine({#"New Header", #"Data Rows"}),
#"Promoted Headers" = Table.PromoteHeaders(#"New Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
You'll need to change the Source line to your file path on your PC.
Here is what I did:
You will have to tweak the M code in the formula bar for this to work. The UI buttons will not let you jump around in steps like I am doing here.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingA few things:
If that isn't waht you need, share your data in an XLSX file. I don't need Power BI. I can write the query in Excel's Power Query tool, which you can then just move to Power BI later.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you for your reply
with regard:
I am trying to clean tha data in power Bi where i should have the correct column headers and leave the excelsheet file as the same structure (other people updateing it and they are used to the look and feel of that excelsheet file)
This is much more difficult than it should be because of how the file is laid out, but I get your users are comfortable with that structure. A true Excel Table would make this a non-issue.
Paste this code into a blank query in Power Query.
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive - eHansalytics\Downloads\File2.xlsx"), null, true),
#"MP Date_Sheet" = Source{[Item="MP Date",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"MP Date_Sheet",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}}),
#"Kept First Rows" = Table.FirstN(#"Changed Type",5),
#"Filled Down" = Table.FillDown(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36"}),
#"New Header" = Table.LastN(#"Filled Down", 1),
#"Data Rows" = Table.Skip(#"Changed Type", 5),
#"New Table" = Table.Combine({#"New Header", #"Data Rows"}),
#"Promoted Headers" = Table.PromoteHeaders(#"New Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
You'll need to change the Source line to your file path on your PC.
Here is what I did:
You will have to tweak the M code in the formula bar for this to work. The UI buttons will not let you jump around in steps like I am doing here.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you so much for the help you gave me, I greatly appreciate the assistance you have provided me.
Great @2019 - hopefully enough info for you to proceed. If you have any sticky issues with a particular worksheet, let us know.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDear@edhans
Now I have another challenge which I tried to accomplish but without any success,
I want to combine 5 tables
(see below)
into new one table where I select which column to bring
(see below)
under one condition if a certain column value equal to certain text
(see below)