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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
az38
Community Champion
Community Champion

Get data from single cell in DataSet (M Language)

Hello!

 

I have a large Excel file with structure like this

 

Снимок.PNG

 

During file load procedure i want to define report_date (16/02/2019), create new column and fill it with this report_date. After that i will delete 5 first row, then promote first row as headers, etc...

 

Unfortunately, I have no idea how i can define it inside Advance Editor

let
    Source = Excel.Workbook(File.Contents("C:\demo.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}})
in
    #"Changed Type1"

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

Try this M code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1]="Report_Date" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1]="Col1" then "Report_Date" else [Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col1", Int64.Type}, {"Col2", type text}, {"Col3", type text}, {"Report_Date", type datetime}})
in
    #"Changed Type1"

This is the result i get

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
pianorb
New Member

Hello Everyone, 

 

Seeking for help, what if i have 2 or more cells that i wanted to be on a separate column?

1st Column - Date and 2nd Column - Split/Skill 

 

sample_.JPG

Ashish_Mathur
Super User
Super User

Hi,

 

Try this M code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1]="Report_Date" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1]="Col1" then "Report_Date" else [Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",5),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col1", Int64.Type}, {"Col2", type text}, {"Col3", type text}, {"Report_Date", type datetime}})
in
    #"Changed Type1"

This is the result i get

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thx!

Solution looks light and easy and works prety well!


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
MFelix
Super User
Super User

 

Hi @az38 ,

 

In Power query you can reference previous steps on your query to get a result.

 

I made a query that gets the report as you need (only difference is the source).

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnIN8A8Kifdz9HVV0lECoVidaCUkJlSBi2MISIGhmb6Bkb6RgaElTN7RxcUzxNPfz9FHwdPPzR+7Ic75OYZANpAyglDGYGGQWCIIG4K5ILkkENcIzDUGMpNBXIhiEyAzBcQ1AXNNgcxUENdUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 6 then #"Added Index"{[Index] -4 } [Column1] else 

if [Index] = 7 then #"Added Index"{[Index] -5 } [Column2] else null),
    Remove_Top_Rows = Table.Skip(#"Added Custom1",5),
    Fill_Down = Table.FillDown(Remove_Top_Rows,{"Custom"}),
    Promote_Headers = Table.PromoteHeaders(Fill_Down, [PromoteAllScalars=true]),
    Remove_Index = Table.RemoveColumns(Promote_Headers,{"6"}),
    #"Changed Type" = Table.TransformColumnTypes(Remove_Index,{{"Col1", Int64.Type}, {"Col2", type text}, {"Col3", type text},  {"REPORT_DATE", type date}})
in
    #"Changed Type"

 

 

Explanation:

  • Add and Index Column (Just to reference data)
  • Add Custom columns with the following code:

 

if [Index] = 6 then #"Added Index"{[Index] -4 } [Column1] else 

if [Index] = 7 then #"Added Index"{[Index] -5 } [Column2] else null

 

  • What this formula does is to check if row as index 6 and get the number 4 rows above of column 1 (REPORT_DATE) and if is 7 then get value 5 rows above columns 2 (16/02/2019) else gives null values

 

  • Remove top rows
  • On column Report_Date do a fill down
    • Rigth click the Report Date column and choose Fill down
  • Promote Headers
  • Format the data

 

Check the PBIX file attach.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.