CSV file from SAP needs Transforming

Power Query
1

Hello attached a simple csv file ( part from a long list )
I want to transfrom it to a workable list. Ideal the account number and the account name should be added in two seperate columns so I ca

sample CSV file for transforming.csv (6.5 KB)

screenshot for transforming csv file
screenshot for transforming csv file1020×280 26.5 KB

2

Hi @Roger ,

Based on your sample and structure that you provide - you need couple of custom column and transformation:

  • Remove Blank Rows

  • Remove Blank in BookDate column with null

  • Add New Column Account number

if [Boekingsdatum] is null then [Vervaldatum] else null
  • Add New Column Account description
if [Boekingsdatum] is null then [Opmerkingen] else null
  • Fill down transformation in both columns

image

(prerequisites: to fulfil column with null values as you can see it in custom column formula “else null” )

  • Remove rows where BookDate is null
    (does not equal null

image
image

sample pbix (Query Transformation)
Sample_CSV.pbix (46.6 KB)

Hope that are what you are looking for.
If not, can you let us know what additional assistance you need?
Thank you!

3

Hello,

thanks for the reply,
I figured that out and it works well , but I’m interested in the M code for solving similar lists soI can apply it

kind regards

roger

4

Hi @Roger ,

What I propose is to watch Melissa’s great M course

Applied Problem Solving with Power Query/M

especially Convert a PDF to a Tabular Format part:

where she explain different scenarios and M possibilities.

Also first M course

Fundamentals In Power Query And M

is great introduction to this one.

Enjoy.

5

Maja

thanks for the tip ,will certainly look at it

happy new year and the best wishes
Roger