Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 seems like some kind or other error. Any help would be much apprecated.
#"Replaced Value" = Table.ReplaceValue(#"Replaced Value10",each [_3],each if [Object] <> "" then [_3] else "----", Replacer.ReplaceValue,{"_3","_4","_5","_6","_7","_8","_9","_10","_11"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value",each [_4],each if [_3] = "" then [_4] else "----", Replacer.ReplaceValue,{"_4","_5","_6","_7","_8","_9","_10","_11"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value7",each [_5],each if [_4] = "" then [_5] else "----", Replacer.ReplaceValue,{"_5","_6","_7","_8","_9","_10","_11"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value6",each [_6],each if [_5] = "" then [_6] else "----", Replacer.ReplaceValue,{"_6","_7","_8","_9","_10","_11"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value5",each [_8],each if [_6] = "" then [_8] else "----", Replacer.ReplaceValue,{"_7","_8","_9","_10","_11"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value4",each [_9],each if [_8] = "" then [_9] else "----", Replacer.ReplaceValue,{"_9","_10","_11"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value3",each [_10],each if [_9] = "" then [_10] else "----", Replacer.ReplaceValue,{"_10","_11"}),
Solved! Go to Solution.
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
Hello @PowerQPro
i don't think that applying your logic there is a way to do that in one step with Table.ReplaceValue, because your are referencing in one step a column, that was changed in the previous step. But if you would do with Table.TransformRows it could work.
Here an code example how this could look like. This is much more readable and logic to maintain
let
Source = #table
(
{"_3","_4","_5","_6","_7","_8","_9","_10","_11"},
{
{"x","","asdf","sadf","1333,33","","asfd","",""}, {"x","","sadf","","sadf","","","sadf",""}
}
),
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
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query
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
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
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
Hi @PowerQPro ,
It seems that you want to simplify this in one step, right? But in your scenario, these steps will refer to previous step, so I think it is hard to complete it in a simple step.
Or you also could @ super users in this forum for more suggestions.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |