Forum Discussion

PowerQPro's avatar
PowerQPro
Frequent Visitor
6 years ago
Solved

Replace Value using a value in different column in PowerQ

Hi   I am trying to reduced the number of line items in my Query.   This section works perfectly, but I think I should be able to reduce these steps to one using else if. Tried various codes and ...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello PowerQPro 

     

    your code should look something like this. I just replaced my datasoure with your steps till the transformation. 

    let
let
    FullPathToFile2 = Excel.CurrentWorkbook(){[Name="FilePath2"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(FullPathToFile2),[Delimiter="#(tab)", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Removed Top Rows" = Table.Skip(Source,13),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Top Rows1" = Table.Skip(#"Promoted Headers",2),
	TransformRows = Table.FromRecords(Table.TransformRows
	(
		#"Removed Top Rows1",
		(row)=> 
		let 
			check3Fill4 = if row[_3]<> "" then Record.TransformFields(row, {"_4", each  "----"}) else row,
			check4Fill4 = if check3Fill4[_4]<> "" then Record.TransformFields(check3Fill4, {"_5", each  "----"}) else check3Fill4
		in 
			check4Fill4

	))
in
	TransformRows

    you have to update this part according you needs. These two lines should show how to get there


    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

     

PowerQPro's avatar
PowerQPro
Frequent Visitor
6 years ago

Hi Jimmy

 

I am not an expert user to identify all the steps you created, but once embeded to the code, it is not giving any Syntax erro, but running creates an error. full code below. Please note that there are lot of columns in the table I did not mention but only need to change the onces I mentioned.

 

let
    FullPathToFile2 = Excel.CurrentWorkbook(){[Name="FilePath2"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(FullPathToFile2),[Delimiter="#(tab)", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Removed Top Rows" = Table.Skip(Source,13),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Top Rows1" = Table.Skip(#"Promoted Headers",2),

 TransformRows = Table.FromRecords(Table.TransformRows
 (
  Source,
  (row)=>
  let
   check3Fill4 = if row[_3]<> "" then Record.TransformFields(row, {"_4", each  "----"}) else row,
   check4Fill4 = if check3Fill4[_4]<> "" then Record.TransformFields(check3Fill4, {"_5", each  "----"}) else check3Fill4
  in
   check4Fill4

 ))
in
 TransformRows

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello PowerQPro 

 

your code should look something like this. I just replaced my datasoure with your steps till the transformation. 

let
let
    FullPathToFile2 = Excel.CurrentWorkbook(){[Name="FilePath2"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(FullPathToFile2),[Delimiter="#(tab)", Columns=29, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Removed Top Rows" = Table.Skip(Source,13),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Top Rows1" = Table.Skip(#"Promoted Headers",2),
	TransformRows = Table.FromRecords(Table.TransformRows
	(
		#"Removed Top Rows1",
		(row)=> 
		let 
			check3Fill4 = if row[_3]<> "" then Record.TransformFields(row, {"_4", each  "----"}) else row,
			check4Fill4 = if check3Fill4[_4]<> "" then Record.TransformFields(check3Fill4, {"_5", each  "----"}) else check3Fill4
		in 
			check4Fill4

	))
in
	TransformRows

you have to update this part according you needs. These two lines should show how to get there


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