Forum Discussion
Helper IIIneed help - Power query or M code solution needed
Hi ashwinkolte,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Helper III
First of all thanks for responding
I saw the PBIX file and M code . if I am not wrong this code is using hardcoded column names . What if there there come additional ones which would come in the future in the input ? Will this work ?
#"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Column2"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","",null,Replacer.ReplaceValue,{"Column3"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","",null,Replacer.ReplaceValue,{"Column4"}),
#"Transposed Table1" = Table.Transpose(#"Replaced Value3"),
#"Renamed Columns" = Table.RenameColumns(#"Transposed Table1",{{"Column1", "Monitor"}, {"Column4", "Monitor_group"}, {"Column2", "Usergroup"}, {"Column3", "Poller"}, {"Column5", "Contact"}}),
#"Filled Down" = Table.FillDown(#"Renamed Columns",{"Poller", "Column6"}),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Monitor", "Monitor_group", "Usergroup", "Poller", "Contact", "Column6"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Column6", "Portnumber"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Monitor", "Monitor_group", "Usergroup", "Poller", "Portnumber", "Contact"})
in
#"Reordered Columns1"
Community SupportHi ashwinkolte,
Thank you for your detailed observation. You are right to raise this concern.
The current M code relies on hardcoded column names such as "Column1", "Column2", etc. This method can lead to issues if the input file structure changes in the future (e.g., new columns are added or the column order changes), as the transformation steps may not function as expected or may even cause errors.
To make the ReplaceValue step future proof and apply it across all columns dynamically (including any that may be added later), you can use the code below. It will replace empty strings ("") with null across the entire table without needing to update the column list manually:
ReplaceBlanksWithNulls = Table.ReplaceValue(
#"Transposed Table",
"",
null,
Replacer.ReplaceValue,
Table.ColumnNames(#"Transposed Table")
)
This ensures that any new columns added to the source data will automatically be included in the transformation no code changes needed.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
- v-kpoloju-msft
Hi ashwinkolte,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-kpoloju-msft
Hi ashwinkolte,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.- ashwinkolte
I was out of town for last few days . Let me try your solution over the weekend . Will definately get back to you .
Thanks for following up !
- ashwinkolte
Hi v-kpoloju-msft If possible can you pls send me PBIX with the change you mentioned which will make it completely dynamic